ALLEXCEPT Highest revenue and Category of the day per Customer

[propi_]

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.

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

<tbody>
</tbody>

All is given except

MaxCategory

Revenue_of_max_category

<tbody>
</tbody>

-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!

 

[Ozeroth]

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)

=
    CALCULATE (
        MAXX (
            VALUES ( YourTable[Category] ),
            CALCULATE ( SUM ( YourTable[Revenue] ) )
        ),
        ALLEXCEPT ( YourTable, YourTable[Customer_id], YourTable[Date] )
    )

MaxCategory (calculated column)

=
    CALCULATE (
        FIRSTNONBLANK (
            TOPN (
                1,
                VALUES ( YourTable[Category] ),
                CALCULATE ( SUM ( YourTable[Revenue] ) )
            ),
            1
        ),
        ALLEXCEPT ( YourTable, YourTable[Customer_id], YourTable[Date] )
    )

 

[propi_]

Dear Owen,

code works flawless and superfast! Big thank you for that! DAX is the way to go
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:

    =[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:

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

<tbody>
</tbody>

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:

=[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

 

[propi_]

Ok, Measure worked for me. Thread closed! Thanks

 

[Ozeroth]

Just looking at this - that's good news!