Search code examples
powerbidaxpowerpivot

Power BI - How to find a maximum value from a list of sum

In PowerPivot function or Power BI, for data set

article channel qty
1   a   5
1   b   8
1   c   10
2   a   6
2   b   9
2   c   12

I want to create a measure "Maximum net" stands for the maximum net qty of 2 articles in all channels(including "a", "b" & "c"). How to make the measure first sum up all the qty of 1 & 2 articles then find the maximum value of the 2 sums?

I tried to use the following DAX code

=MAXX(VALUES("table[article]"),SUM([qty]))

but the final output is 50. What I suppose the formula do should be the first sum on 2 articles get the "5+8+10=23" & "6+9+12=27", then find the maximum of "23" & "27" and finally get "27"

Solution

  • The DAX below first groups on article and then takes the max:

    Measure = MAXX(GROUPBY('table';'table'[article];"total";SUMX(CURRENTGROUP();'table'[qty]));[total])

    You can also go with a seperate table and use this:

    ArticleTable = GROUPBY('table';'table'[article];"total";SUMX(CURRENTGROUP();'table'[qty]))