Need Assistance: Filtering Top 5 Customers by Margin % with Unit Requirement Challenge

[gmat]

I'm currently working on creating a matrix that displays the top 5 customers based on Margin %, but I'm encountering a challenge. I want to include only those customers who have 5 or more units. Unfortunately, the current setup isn't populating the field with any information.

I suspect the issue arises because the top 5 customers by Margin % in the entire dataset have fewer than 5 units. Ideally, I would like to narrow down the population, filtering it to include only customers with 5 or more units, and then determine the top 5 based on Margin % within that subset.

Has anyone encountered a similar situation or have suggestions on how to address this?

Thanks in advance for your help!

[jdbuchanan71 · Accepted solution]

You can use your units measure as a filter on your TOPN like this.

Top 5 Margin % = 
CALCULATE (
    [Margin %],
    KEEPFILTERS (
        TOPN (
            5,
            FILTER ( ALL ( Customer[CustomerKey] ), [Units] > 5 ),
            [Margin %]
        )
    )
)

[gmat]

Can I also use this for bottom 5?

[jdbuchanan71]

Yep, you just need to change the sort order, like this.

Bottom 5 Margin % = 
CALCULATE (
    [Margin %],
    KEEPFILTERS (
        TOPN (
            5,
            FILTER ( ALL ( Customer[CustomerKey] ), [Units] > 5 ),
            [Margin %],
            ASC
        )
    )
)