Top N Customers based on the Sales of latest Fiscal Qtr

[clickerclick999]

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
)

[v-sgandrathi]

Hi @clickerclick999,

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.

[v-sgandrathi]

Hi @clickerclick999,

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.

[v-sgandrathi]

HI @clickerclick999,

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.

[maruthisp · Accepted solution]

Hi @clickerclick999,

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

[v-sgandrathi]

Hi @clickerclick999,

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.

[clickerclick999]

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?

[johnt75]

What is the definition of [Latest Quarter Sales] ?

[clickerclick999]

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

[johnt75]

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

[clickerclick999]

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

[johnt75]

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.