topic Re: Top N Customers based on the Sales of latest Fiscal Qtr in DAX Commands and Tips

Top N Customers based on the Sales of latest Fiscal Qtr

Anonymous — Fri, 09 May 2025 09:47:49 GMT

Trying to create a Top N Slicer with customers being ranked by their latest quarter sales number with output of their Accounts Receivables.

Table of Customers and their sales number. Based on FY25 Q4 (010/2025 and 011/2025), Ignite is ranked 1.

Slicing for top 1, I would only want Ignite to show up in the Open AR table using measure below. But currently, my measure is re-evaluating for each fiscal month instead of being fixed ranking based on latest Fiscal qtr. How to proceed?

Open AR TopN = VAR TopNValue = SELECTEDVALUE('TopN Selection'[TopN Selection]) VAR LatestQtr = [Latest Fiscal Quarter] // Customer rank for row level VAR CustomerRank = RANKX( ALLSELECTED(Combined[Customers]), [Latest Quarter Sales], , DESC ) // Build Top N customer list for total level VAR TopNCustomers = TOPN( TopNValue, SUMMARIZE( ALL(Combined[Customers]), Combined[Customers], "Sales", [Latest Quarter Sales] ), [Latest Quarter Sales], DESC ) // Total Open AR for Top N Customers VAR TotalOpenAR = CALCULATE( SUM(Combined[Amount]), Combined[Category] = "Open AR", FILTER( ALL(Combined[Customers]), Combined[Customers] IN SELECTCOLUMNS(TopNCustomers, "Customer", Combined[Customers]) ) ) RETURN IF( HASONEVALUE(Combined[Customers]), -- Row level: check rank IF( CustomerRank <= TopNValue, CALCULATE( SUM(Combined[Amount]), Combined[Category] = "Open AR" ) ), -- Total level TotalOpenAR )

Re: Top N Customers based on the Sales of latest Fiscal Qtr

johnt75 — Fri, 09 May 2025 10:14:36 GMT

What is the definition of [Latest Quarter Sales] ?

Re: Top N Customers based on the Sales of latest Fiscal Qtr

v-sgandrathi — Mon, 12 May 2025 12:21:14 GMT

Hi Anonymous,

To resolve the issue, you need to fix the ranking context so that it is based solely on total sales from the latest fiscal quarter, rather than recalculating for each individual month for that it may be required re write the DAX logic from the beginning. This ensures that the Top N logic is evaluated just once using the most recent quarter’s sales figures and then applied consistently across all rows in your visual, regardless of other filters or breakdowns like fiscal month.

Please Accept as solution if this meets your needs and a Kudos would be appreciated.

Thank you.

Re: Top N Customers based on the Sales of latest Fiscal Qtr

Anonymous — Wed, 14 May 2025 01:55:40 GMT

Latest Quarter Sales = CALCULATE( SUM(Combined[Amount]), Combined[Category] = "Sales", TREATAS({[Latest Fiscal Quarter]}, Combined[Fiscal Quarter]) )

Re: Top N Customers based on the Sales of latest Fiscal Qtr

Anonymous — Wed, 14 May 2025 01:56:55 GMT

I am struggling with how I should be re writing the logic such that the TOP N logic is evaluated only once using the most recent quarter's sales figure. Any help?

Re: Top N Customers based on the Sales of latest Fiscal Qtr

maruthisp — Wed, 14 May 2025 04:20:22 GMT

Hi Anonymous,

As per my understanding, I tried to regenerate the issue and try to fix the issue.
Please find the atatched pbix file for your reference.
Top N Customers based on the Sales of latest Fiscal Qtr.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!

Best Regards,

Maruthi

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/

X - Maruthi Siva Prasad - (@MaruthiSP) / X

Re: Top N Customers based on the Sales of latest Fiscal Qtr

johnt75 — Wed, 14 May 2025 08:36:37 GMT

OK, that seems fine. What is the definition of [Latest Fiscal Quarter], and any measures that that depends on ?

Re: Top N Customers based on the Sales of latest Fiscal Qtr

v-sgandrathi — Thu, 15 May 2025 07:20:55 GMT

Hi Anonymous,

Have you got a chance to review the maruthisp answer

I'm glad to hear that your query was resolved! If the response provided by the community member addressed your concern, kindly confirm.

Marking it as Accept Answer and give us Kudos if you found it helpful allows us to ensure that the solutions shared are valuable for the entire community.

Re: Top N Customers based on the Sales of latest Fiscal Qtr

Anonymous — Thu, 15 May 2025 08:47:20 GMT

Latest Fiscal Quarter = CALCULATE( MAX(Combined[Fiscal Quarter]), FILTER( ALL(Combined), Combined[Category] = "Sales" ) )

Re: Top N Customers based on the Sales of latest Fiscal Qtr

johnt75 — Thu, 15 May 2025 10:02:53 GMT

The only issue that I see is that everything is in the same table, so it is possible that autoexists is causing a problem. You can read more about it at Understanding DAX Auto-Exist - SQLBI

I would create a proper date table, marked as a date table, and use columns from that in your visuals and measures.

Re: Top N Customers based on the Sales of latest Fiscal Qtr

v-sgandrathi — Sun, 18 May 2025 11:20:37 GMT

Hi Anonymous,

As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?

And if the provided information meets your requirements, you can Accept the solution and also give Kudos on that reply. It helps other users who are searching for this same information and find the information.

Your understanding and patience will be appreciated.

Re: Top N Customers based on the Sales of latest Fiscal Qtr

v-sgandrathi — Wed, 21 May 2025 11:58:36 GMT

HI Anonymous,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you for using Microsoft Fabric Community Forum.