ALLEXCEPT Highest revenue and Category of the day per Customer

P

propi_

New Member
Joined
Jun 11, 2014
Messages
19
Dear Dax Pro's

i have a Case my DAX Knowledge exceeds. I have a Dataset, and i want to use the ALLEXCEPT in a more complex way.

[TABLE="width: 902"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Customer_id[/TD]
[TD]Order_of_n_day[/TD]
[TD]OrderDay[/TD]
[TD]Revenue[/TD]
[TD]Date[/TD]
[TD]Order no[/TD]
[TD]Category[/TD]
[TD]MaxCategory
[/TD]
[TD]Revenue_of_max_category
[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29.07.2015 00:00[/TD]
[TD="align: right"]885689[/TD]
[TD]V[/TD]
[TD]P[/TD]
[TD="align: right"]49,19[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]22,36[/TD]
[TD="align: right"]29.07.2015 00:00[/TD]
[TD="align: right"]885690[/TD]
[TD]F[/TD]
[TD]P[/TD]
[TD="align: right"]49,19[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]49,19
[/TD]
[TD="align: right"]29.07.2015 00:00[/TD]
[TD="align: right"]885691
[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD="align: right"]49,19[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]18,02[/TD]
[TD="align: right"]29.07.2015 00:00[/TD]
[TD="align: right"]885692[/TD]
[TD]F[/TD]
[TD]P[/TD]
[TD="align: right"]49,19[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]108,4[/TD]
[TD="align: right"]01.09.2015 00:00[/TD]
[TD="align: right"]885693[/TD]
[TD]S[/TD]
[TD]S[/TD]
[TD="align: right"]108,4[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]07.11.2016 00:00[/TD]
[TD="align: right"]885694[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD="align: right"]127[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]60,17[/TD]
[TD="align: right"]07.11.2016 00:00[/TD]
[TD="align: right"]885695[/TD]
[TD]P[/TD]
[TD]V[/TD]
[TD="align: right"]127[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]07.11.2016 00:00[/TD]
[TD="align: right"]885696[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD="align: right"]127[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]200
[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29.08.2015 00:00[/TD]
[TD="align: right"]885697[/TD]
[TD]V[/TD]
[TD]P[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]22,36[/TD]
[TD="align: right"]29.08.2015 00:00[/TD]
[TD="align: right"]885698[/TD]
[TD]F[/TD]
[TD]P[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]29.08.2015 00:00[/TD]
[TD="align: right"]885699[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]18,02[/TD]
[TD="align: right"]29.08.2015 00:00[/TD]
[TD="align: right"]885700[/TD]
[TD]F[/TD]
[TD]P[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]01.10.2015 00:00[/TD]
[TD="align: right"]885701[/TD]
[TD]S[/TD]
[TD]S[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]17.11.2016 00:00[/TD]
[TD="align: right"]885702[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD="align: right"]227[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]60,17[/TD]
[TD="align: right"]17.11.2016 00:00[/TD]
[TD="align: right"]885703[/TD]
[TD]P[/TD]
[TD]V[/TD]
[TD="align: right"]227[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]17.11.2016 00:00[/TD]
[TD="align: right"]885704[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD="align: right"]227
[/TD]
[/TR]
</tbody>[/TABLE]

All is given except
[TABLE="width: 902"]
<tbody>[TR]
[TD]MaxCategory
[/TD]
[TD]Revenue_of_max_category
[/TD]
[/TR]
</tbody>[/TABLE]


-My Problem is, I want to find for each Customer the Category with the highest Revenue on the Order Day, flag all other with that Category and also want to know that Revenue.
-The other Problem is, if there has been 2 Orders of the same category on that day, it should be summarized first in the calculation (Highest revenue of that Category of the day per Customer).

Is this even possible in DAX in a performant way? ALLEXCEPT and EARLIER worked pretty fast for me so far. Maybe it's more a "SQL Rank over Partition by" thing :)

Thank you guys in advance! You are the best!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Hi propi_,

First off, you should probably create measures rather than calculated columns for this sort of thing.
Calculated Columns vs Measures in DAX - Excelerator BI

In any case, here's how you could create the calculated columns. Measures would be similar but different since there would be no row context and you would have to decide how to handle multiple date/customer selection.

Revenue_of_max_category (calculated column)
Code: 
=
    CALCULATE (
        MAXX (
            VALUES ( YourTable[Category] ),
            CALCULATE ( SUM ( YourTable[Revenue] ) )
        ),
        ALLEXCEPT ( YourTable, YourTable[Customer_id], YourTable[Date] )
    )

MaxCategory (calculated column)
Code: 
=
    CALCULATE (
        FIRSTNONBLANK (
            TOPN (
                1,
                VALUES ( YourTable[Category] ),
                CALCULATE ( SUM ( YourTable[Revenue] ) )
            ),
            1
        ),
        ALLEXCEPT ( YourTable, YourTable[Customer_id], YourTable[Date] )
    )
 
Upvote 0
Dear Owen,

code works flawless and superfast! Big thank you for that! DAX is the way to go :cool:
You're right. Measures would be better (at least for the Max Revenue). Dimensions as the category are more "dynamic" when put to the measurelines in my opinion.

How do i read the first code?: Calculate the max value (revenue) for the categories over customer and date?


I have also calculated the daily revenue as follows:

Code: 
    =[COLOR=#0000ff]CALCULATE[/COLOR](
                [COLOR=#0000ff]SUM[/COLOR]([COLOR=#000000]yourTable[Revenue][/COLOR]);
                [COLOR=#0000ff]ALLEXCEPT[/COLOR](yourTable;[COLOR=#000000]yourTable[Customer_id][/COLOR];[COLOR=#000000]yourTable[Date][/COLOR])
             )

When put as an measure it works. I know, I shouldn't, but for Evaluation i'd also like to write the daily Revenue only to the first Order of the day like:

[TABLE="width: 906"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Customer_id[/TD]
[TD]Order_of_n_day[/TD]
[TD]OrderDay[/TD]
[TD]Revenue[/TD]
[TD]Date[/TD]
[TD]Order no[/TD]
[TD]Category[/TD]
[TD]MaxCategory[/TD]
[TD]Revenue_of_max_category[/TD]
[TD]Eval_dayRevenue[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]27[/TD]
[TD]29.07.2015 00:00[/TD]
[TD]885689[/TD]
[TD]V[/TD]
[TD]P[/TD]
[TD]49,19[/TD]
[TD="align: right"]116,57[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]22,36[/TD]
[TD]29.07.2015 00:00[/TD]
[TD]885690[/TD]
[TD]F[/TD]
[TD]P[/TD]
[TD]49,19[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]100[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]49,19[/TD]
[TD]29.07.2015 00:00[/TD]
[TD]885691[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]49,19[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]100[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]18,02[/TD]
[TD]29.07.2015 00:00[/TD]
[TD]885692[/TD]
[TD]F[/TD]
[TD]P[/TD]
[TD]49,19[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]108,4[/TD]
[TD]01.09.2015 00:00[/TD]
[TD]885693[/TD]
[TD]S[/TD]
[TD]S[/TD]
[TD]108,4[/TD]
[TD="align: right"]108,4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]27[/TD]
[TD]07.11.2016 00:00[/TD]
[TD]885694[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD]127[/TD]
[TD="align: right"]187,17[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]100[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]60,17[/TD]
[TD]07.11.2016 00:00[/TD]
[TD]885695[/TD]
[TD]P[/TD]
[TD]V[/TD]
[TD]127[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]100[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]100[/TD]
[TD]07.11.2016 00:00[/TD]
[TD]885696[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD]127[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]200[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]27[/TD]
[TD]29.08.2015 00:00[/TD]
[TD]885697[/TD]
[TD]V[/TD]
[TD]P[/TD]
[TD]50[/TD]
[TD="align: right"]117,38[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]200[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]22,36[/TD]
[TD]29.08.2015 00:00[/TD]
[TD]885698[/TD]
[TD]F[/TD]
[TD]P[/TD]
[TD]50[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]200[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]50[/TD]
[TD]29.08.2015 00:00[/TD]
[TD]885699[/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD]50[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]200[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]18,02[/TD]
[TD]29.08.2015 00:00[/TD]
[TD]885700[/TD]
[TD]F[/TD]
[TD]P[/TD]
[TD]50[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]200[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]200[/TD]
[TD]01.10.2015 00:00[/TD]
[TD]885701[/TD]
[TD]S[/TD]
[TD]S[/TD]
[TD]200[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]200[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]27[/TD]
[TD]17.11.2016 00:00[/TD]
[TD]885702[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD]227[/TD]
[TD="align: right"]287,17[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]200[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]60,17[/TD]
[TD]17.11.2016 00:00[/TD]
[TD]885703[/TD]
[TD]P[/TD]
[TD]V[/TD]
[TD]227[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]200[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]200[/TD]
[TD]17.11.2016 00:00[/TD]
[TD]885704[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD]227[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


I know that EARLIER() in an IF() clause is the way to go, but now things are getting too complex for me :)

Here's my other EARLIER Code i used:

Code: 
=[COLOR=#0000ff]IF[/COLOR]([COLOR=#000000][id][/COLOR]
      =[COLOR=#0000ff]CALCULATE[/COLOR]([COLOR=#0000ff]Min[/COLOR]([COLOR=#000000][id][/COLOR]);[COLOR=#0000ff]ALL[/COLOR](yourTable);[COLOR=#000000]yourTable[Customer_id][/COLOR] =[COLOR=#0000ff]EARLIER[/COLOR]([COLOR=#000000][Customer_id][/COLOR]));[COLOR=#000000][Revenue][/COLOR];[COLOR=#00008b]0[/COLOR])

But this only works over [id] for all Days, but I want to calculate within days :)

Thanks in advance and have a great day!
-Andy
 
Upvote 0
You must log in or register to reply here.

Similar threads

TheMacroNoob
  • Question Question
Rank by Group - Excluding w/ Criteria
Replies
0
Views
443
TheMacroNoob
TheMacroNoob
P
  • Question Question
Value, Earlier, Allexcept ?
Replies
3
Views
963
propi_
P
V
  • Question Question
How to calculate working hours per Calendar date
Replies
4
Views
607
Sulprobil
Sulprobil
E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
284
Peter_SSs
Peter_SSs
R
  • Question Question
Help!
Replies
3
Views
2K
63falcondude
63falcondude

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top