Syntax for Max of sum distincts

[Ezehc02]

Please I am practicing DAX & need help

How do I create a measure which sum up a column by the distinct values of another column and returns the maximum value of the distinct values.

For instance in table 1

I have 2 columns viz; ID & Quantity

ID	Quantity
AA	2
BB	4
CC	7
AA	2
CC	4
AA	9
AA	2
CC	23
BB	9
BB	5

The measure should return CC as the max of the sum distincts based on Quantity

@amitchandak  and @Jihwan_Kim 

Please help

Thanks alot

[Anonymous · Accepted solution]

Hi  @Ezehc02 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Sum_Group =
CALCULATE(
    SUM('Table'[Quantity]),FILTER(ALL('Table'),
    'Table'[ID]=MAX('Table'[ID])))

Measure =
CALCULATE(MAX('Table'[ID]),FILTER(ALL('Table'),
[Sum_Group]=
MAXX(
    ALL('Table'),[Sum_Group])))

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ezehc02]

Thank you so much @Jihwan_Kim for the help, I learnt alot from your DAX statement and I gave you a thumbs up. But I noticed that when I used this syntax in a table with distinct IDs more than 3, it does not give me the correct figure.

I hoped to get a solution which will sum the unique values, then return the unique ID with the highest sum

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Expected result measure = 
VAR _summarizetable =
    SUMMARIZE ( Data, Data[ID], Data[Quantity] )
VAR _addsumofdistinctquantity =
    SUMMARIZE (
        ADDCOLUMNS (
            _summarizetable,
            "@sumofdistinctquantity",
                SUMX (
                    FILTER ( _summarizetable, Data[ID] = EARLIER ( Data[ID] ) ),
                    Data[Quantity]
                )
        ),
        Data[ID],
        [@sumofdistinctquantity]
    )
VAR _maxsumquantity =
    MAXX ( _addsumofdistinctquantity, [@sumofdistinctquantity] )
RETURN
    SUMMARIZE (
        FILTER ( _addsumofdistinctquantity, [@sumofdistinctquantity] = _maxsumquantity ),
        Data[ID]
    )