Calculate sum of grouped values corresponding on some condition

[drru97]

Hi Everyone,

I'm trying to write a DAX measurement query to get a scalar numeric value of sum of grouped item's column based on max condition with another column.

I have the following table:

For that table, I wanna get sum of 'value' corresponding to max 'order' column for each 'node'.

For example, for 'node' n1 the largest 'order' is 45 and it's corresponding 'value' is 5.

For 'node' n2 it's 46 and corresponding 8.

And the same for third 'node'.

So in a result I wanna calculate the sum of 5, 8 and 9 and get 22.

I'm thinking about the algorithm of doing that:

1. Group by node

2. Get max order for each group

3. Get corresponding value to it.

4. Sum corresponding values.

I tried to write similar algorithm in DAX: 

sumValues = COUNTAX(SUMMARIZE('table', 'table'[node], "maxGroup", MAX('table'[order])), [maxGroup])

but it's not working as expected.

I will greatly appreciate any help and thanks in advance.

Thanks,

Oleh

[v-lid-msft · Accepted solution]

Hi @drru97 ,

We can use following measures to meet your requirement:

Measure =
SUMX (
    DISTINCT ( 'Table'[node] ),
    VAR o =
        CALCULATE ( MAX ( 'Table'[order] ) )
    RETURN
        CALCULATE ( SUM ( 'Table'[value] ), 'Table'[order] = o )
)

Or

Measure 2 =
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        'Table',
        VAR n = [node] RETURN 'Table'[order]
            = CALCULATE ( MAX ( 'Table'[order] ), 'Table', 'Table'[node] = n )
    )
)

By the way, PBIX file as attached.

Best regards,

[drru97]

Thank you @v-lid-msft @camargos88 !

Your solution worked for me 🙂

[camargos88]

Hi @drru97 ,

This measure should help you:

Measure =

VAR _MaxOrder = CALCULATE(MAX('Table (3)'[Order]); ALLEXCEPT('Table (3)'; 'Table (3)'[Node]))

VAR _MaxValue = CALCULATE(MAX('Table (3)'[Value]); FILTER(ALLEXCEPT('Table (3)';'Table (3)'[Node]); 'Table (3)'[Order] = _MaxOrder))

RETURN _MaxValue

This drag it onto a table with nodes values.

Also, if you wanna use summarize, this code should work:

Measure =

VAR _tbl = SUMMARIZE('Table (3)'; 'Table (3)'[Node];

"Max_Value";

CALCULATE(MAX('Table (3)'[Value]); FILTER(ALLEXCEPT('Table (3)';'Table (3)'[Node]); 'Table (3)'[Order] = CALCULATE(MAX('Table (3)'[Order]); ALLEXCEPT('Table (3)'; 'Table (3)'[Node]))

)

)

)

RETURN SUMX(_tbl; [Max_Value])

Ricardo