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

Question

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

GregGalloway · Accepted Answer · 2016-11-13T18:49:40.207

1

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

edited Nov 13 '16 at 18:49

answered Nov 13 '16 at 00:33

GregGalloway

11,355
3
16
47

Thanks for your response. However, I need the "Group By" action of SUMMARIZE. I want to know who has the highest per unit average and regardless of what the FI-Manager context it will always show this highest per unit average (of all FI-Managers). I'm not sure I put it right in the question. – John Donnelly Nov 13 '16 at 17:33
@JohnDonnelly I believe that's what my answer is doing. If it doesn't return the right number please explain more – GregGalloway Nov 13 '16 at 17:35
Your answer returned the MAX average of all the FI-Managers. It was always the same irrespective of the context but I wanted the MAX average of the best FI-Manager. – John Donnelly Nov 13 '16 at 17:42
The good news is that just substituting DIVIDE for the division operator worked! I guess DIVIDE is critical. – John Donnelly Nov 13 '16 at 17:43
@JohnDonnelly define "best" FI-Manager please – GregGalloway Nov 13 '16 at 17:46
@JohnDonnelly that's what my calc returns. It loops over every FI-Manager and calculates the AvgPerUnit at that grain then returns the max (best) – GregGalloway Nov 13 '16 at 18:13
Ok! I re did it with the two separate measures and your solution works just fine. When I combined them I did something wrong. Thanks for your help. – John Donnelly Nov 13 '16 at 18:48

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

1 Answers1