Forum Discussion
DAX with SUM and MAX
Hello,
I've got a Pivot Table constructed like this
How can I get max of summed values of that field?
I've tried this but it fails.
=MAXX(SUMMARIZE(Table31,[Output]))
Let take such sample
You may create measures
V, sum:=CALCULATE( SUM(Table1[V] ), ALLEXCEPT(Table1, Table1[A], Table1[B] ) ) V, max Sum:=MAXX( ALLEXCEPT(Table1, Table1[A] ), [V, sum] ) Max of Sum:=IF( HASONEVALUE(Table1[B]), [V, sum], [V, max Sum] )and use the latest one in PivotTable.
3 Replies
Let take such sample
You may create measures
V, sum:=CALCULATE( SUM(Table1[V] ), ALLEXCEPT(Table1, Table1[A], Table1[B] ) ) V, max Sum:=MAXX( ALLEXCEPT(Table1, Table1[A] ), [V, sum] ) Max of Sum:=IF( HASONEVALUE(Table1[B]), [V, sum], [V, max Sum] )and use the latest one in PivotTable.
Thank you for your response. You are everywhere and helping everyone out. I see your name often in almost every thread. I really appreciate it.
Okay, I managed to get the formulas working without errors. However,
The max value doesn't display next to the group.
Max of Sum: =IF( HASONEVALUE(Table31[Name]), [V, sum], [V, max Sum] ) V, max sum: =MAXX( ALLEXCEPT(Table31, Table31[Group] ), [V, sum] ) V, sum: =CALCULATE( SUM(Table31[Output] ), ALLEXCEPT(Table31, Table31[Group], Table31[Name] ) ) A is GROUP B is Name V is OutputHowever,
I don't need to display it in the pivot. I used cubevalue function which I learnt from you and it retrieved the data I wanted. Thanks a lot!!!
Thank you. I'm not alone people, lot of clever people who are quite active with answers.
As for the PivotTable perhaps you shifted off subtotals for groups
