cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## DAX Total SUM of Max version incorrect

I've been struggling to get the total sum working using the following measure:

CALCULATE( SUM(table1[value]) ,table1[version]=MAX(table1[version] ) )

This works fine on a row level, but the totals only return the max version of the total set. I've also tried to change the DAX to
SUMX(values(Table1[No]), CALCULATE(SUM(table1[value]),table1[version]=MAX(table1[version]))) but I got the same result for the total: 390 instead of 110+190+300+390 = 990

I have to keep the version numbers in the dataset so filtering the max version in Power Query is not an option. Anyone have any ideas?

 Table1 no value version 1 100 1 1 110 2 2 200 1 2 190 2 3 300 1 3 300 2 4 400 1 4 450 2 4 390 3 Result No value 1 110 2 190 3 300 4 390 Total 390

1 ACCEPTED SOLUTION
Community Champion

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
5 REPLIES 5
Community Champion

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Frequent Visitor

great...that worked 🙂

Community Champion

Hi @joris ,

Try these two measures:-

``````Measure_2 =
VAR max_version =
CALCULATE ( MAX ( table1[version] ), ALL ( table1[version] ) )
RETURN
CALCULATE (
SUM ( table1[value] ),
FILTER ( table1, table1[version] = max_version )
)``````

``````Measure_3 =
SUMX (
SUMMARIZE ( table1, table1[no], "total", table1[Measure_2] ),
[total]
)``````

Output:-

Thanks,

Samarth

Best Regards,
Samarth

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

Frequent Visitor

That didn't work unfortunately. I still get the wrong total:

Super User

@joris , Try a measure like

var _vrsion = calculate(MAX(table1[version]),Filter(allselected(Table) ,table1[No] = max(Table[No] )))
return
CALCULATE( SUM(table1[value]) ,Filter(allselected(Table), table1[version]=_vrsion && table1[No] = max(Table[No] )))