DAX Total SUM of Max version incorrect

[joris]

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

[CNENFRNL · Accepted solution]

[joris]

great...that worked 🙂

[Samarth_18]

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

[joris]

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

[amitchandak]

@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] )))