Search code examples
powerpivotdax

How to use DAX function ALL() with SUMMARIZE()

The following formula returns a scalar value which is the maximum value of the calculation (AvgPerUnit). However, I don't want the maximum value to change based on the context. I always want the value to be based on ALL rows.

HighestAvgPerUnit:=MAXX(
    SUMMARIZE('Table1','Table1[FI-Manager],"AvgPerUnit",
        SUM('Table1[Income])/SUM('Table1[IsDeal])),
    [AvgPerUnit])

My question is: how to use ALL() in the above formula to get the MAX scalar value like I get with the above using all rows?

I tried the following and I get a Table that I don't know what to do with:

HighestAvgPerUnit:=MAXX(
    SUMMARIZE(ALL('Table1'),'Table1[FI-Manager],"AvgPerUnit",
        SUM('Table1[Income])/SUM('Table1[IsDeal])),
    [AvgPerUnit])
Solution

  • Try the following. I have split into two calculated measures:

    AvgPerUnit:=DIVIDE(SUM('Table1'[Income]),SUM('Table1'[IsDeal]))

HighestAvgPerUnit:=MAXX(
    ALL('Table1'[FI-Manager]),
    [AvgPerUnit]
)