CODEWITHSUNDEEP
powerpivotdax
John Donnelly
John Donnelly

Reputation: 917

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

Upvotes: 0

Views: 2256

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

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

Upvotes: 1

Related Questions