I need to calculate the maximum sales by any account in a user-specified geography (zip code, county, state, etc), 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 suggestion for an alternative DAX expression with better performance?
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])
For background info … The “Market” table has 3 columns, ZipCode, AccountNumber, and Sales. This table has a relationship to my store-list lookup table on the zip code. I have a larger primary fact table that has a relationship to the store-list lookup table on StoreNumber. Why this odd data model? Because in the fact table, I have store-level granularity, and only stores that belong to particular chains. In the market table, it has ALL stores, but the granularity only goes down to the chain owner, not store level. What I need to happen is for the user to select the account and geography, and that will filter the market table to only zip codes where the selected account has stores, then my calculation will return the maximum sales of any chain in my market table, excluding the chain that’s been selected. My data model and the expression above satisfies the requirement, but it’s very slow.