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