Optimized alternative to Maxx (Summarize)

DAX DAX Calculations
1

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. :frowning:

2

@Eric

Thanks for posting the question on the forum. If you can share the PBIX (original or sample) file, the solution can be provided effectively as it will be easier to debug the slow performance of the measure.

Thanks.

3

Hi @Eric, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

4

@MudassirAli - Sorry, attached is a sample workbook. I tried using the same expression that I explained in my original post, but it’s returning blanks. Essentially, in this sample data I am trying to calculate the maximum sales contribution of a customer to a geography, excluding the customer in scope. See below for an illustration.

image
image1962×790 370 KB
Free training Power BI Demo.pbix (504.2 KB)

5

@Eric

Please try this measure and let me know. I haven’t tested it yet but I believe it will work.

MaxContributionbyCity =
CALCULATE (
    MAXX ( ALLSELECTED ( Customer[Customer Name] ), [% Sales Contribution] ),
    ALLEXCEPT ( Location, Location[City] )
)

Thanks.

1 Like