Optimized MAXX & SUMMARIZE Expression

Question

I need to calculate the maximum sales by any account in a user-specified geography, excluding the account in the filter or in the row. I have an expression that works but its very sluggish and I wonder if there's opportunity for optimization.

When I tried to use the regular MAX, it appeared limited to row values and would not return an aggregate max. For example, it worked fine at the zip code level, but when I viewed by state, it was still returning the maximum value of an account's sales at the zip code level rather than returning the maximum aggregate sales by account in a state.

Here's what I am using now. Any thoughts?

Maximum Sales of Any Account =

var _SelectedAccount = if(HASONEVALUE(StoreList[AccountNumber]),firstnonblank(StoreList[LAccountNumber],true),0)

return

maxx(summarize(Market,Market[AccountNumber],"Max Sales", calculate(sum(Market[Sales_Volume]),

filter(all(Market[AccountNumber]),Market[AccountNumber]<>_SelectedAccount))),[Max Sales])

v-easonf-msft · Answer

Hi,&nbsp;Mainer04401&nbsp;
Please check whether these related threads help.
https://powerpivotpro.com/2014/01/another-way-to-find-the-top-performing-thing/&nbsp;
https://powerpivotpro.com/2017/09/dax-reanimator-series-top-performers-using-summarize/&nbsp;
&nbsp;
Best Regards,Community Support Team _ Eason

Forum Discussion

Optimized MAXX & SUMMARIZE Expression

1 Reply

Related content

Optimize SUMX with SUMMARIZE

DAX Optimizing SUMMARIZE()

Optimize SUMX / SUMMARIZE Measure

Optimize Measures

Parameter in summarize