Extract category related to maximum of sum?

[Clara]

Hello guys!

Let's say I have this dataset (called Table1):

Unit	Item	Price
A	Cameras	$   370,40
A	Laptops	$3.200,00
A	Smartphones	$1.250,00
B	Cameras	$   620,80
B	Laptops	$2.700,00
B	Smartphones	$1.240,00
C	Cameras	$   290,00
C	Laptops	$2.300,00
C	Smartphones	$1.600,00
D	Cameras	$            -
D	Laptops	$            -
D	Smartphones	$            -

And I want to extract how much each unit is spending overall (considering all items). I've used a combination of MAXX and SUMMARIZE in a measure: 

VAR sumtable = SUMMARIZE(Table1;Table1[Unit];"Sum Prices";SUM(Table1[Price]))

Which would return this summarized table:

Unit	Sum Prices
A	$       4.820,40
B	$       4.560,80
C	$       4.190,00
D	$                  -

And then

RETURN MAXX(sumtable;[Sum Prices])

Which would return the maximum expense $ 4,820.40.

But now I want to make another measure which would tell me which unit has spent that much in total (which would be Unit A). Any way I can achieve that??

And a follow up question: If I were to fetch which unit has spent the LEAST (ignoring units with zero expense, like Unit D), how would that go?

[v-yulgu-msft · Accepted solution]

Hi @Clara,

Please try this measure:

max unit =
VAR sumtable =
    SUMMARIZE ( data, data[Unit], "Sum Prices", SUM ( data[ Price ] ) )
RETURN
    CALCULATE (
        SELECTEDVALUE ( data[Unit] ),
        FILTER ( sumtable, [Sum Prices] = MAXX ( sumtable, [Sum Prices] ) )
    )

Min unit =
VAR sumtable =
    SUMMARIZE ( data, data[Unit], "Sum Prices", SUM ( data[ Price ] ) )
RETURN
    CALCULATE (
        SELECTEDVALUE ( data[Unit] ),
        FILTER ( sumtable, [Sum Prices] = MINX ( sumtable, [Sum Prices] ) )
    )

Best regards,

Yuliana Gu