How to Show Top 10 Customers by Sales, with Extra Details

[StevenHiatt]

I am trying to create a matrix visual that will show the top 10 customers by sales, but then also show the gross margin dollars and percent for each of those customers. I also need to be able to drill down and show the products purchased by each of those customers. I have seen many related help topics that explain how to do this if you only have the sales column showing, but adding the margin columns seems to add additional complexity.

I have the following metric which gives me the ranking that I can use to find the top 10, and am pretty sure that somehow I will need to add a filter somewhere to limit just those customers to show, but am not sure exactly what to do at that point.

Metric = RANKX( ALL( 'Sales Data'[Customer Name] ), CALCULATE( [Total Sales] ), , DESC, Dense )

Could someone help me with what to do next and how to apply the correct filters?

Thank you so much for your help.

[amitchandak]

@StevenHiatt , You can use measures like

M1= calculate([Sales], keepfilters(topn(10, allselected(Table[Customer Name]), [Sales], desc) )

In drill report use the measure as filter or add measures like

M2= calculate([Margin %], keepfilters(topn(10, allselected(Table[Customer Name]), [Sales], desc) )

All measures in visual should use the keepfilter topn

or use topn visual level filter

TOPN: https://youtu.be/QIVEFp-QiOk

Learn Power BI: RANKX, TOPN, Dynamic TOPN with Numeric Parameters: https://youtu.be/cN8AO3_vmlY?t=25610

[StevenHiatt]

Thanks Amit, this is very helpful. I added the sales metric and it only shows the top 10 customers as expected. However, when I try to drill down to show all the products for that customer it will only show some of the products, not all. I'm guessing it is only showing the top 10 products? How can I get it to show all products, but just for those top 10 customers?

[StevenHiatt]

Here is a screenshot of where I am now:

The first column is a metric of total sales. The second column in the TOPN formula you gave me, which shows correctly at the customer level, but omits totals at the product level (orange area). The last column is a ranking which works correctly at the customer level, but not at the product level. I want the area in red to show 4 (the customer rank) and the green area should be blank as there were no sales of those products in the period.

_Customer Top 10 =
CALCULATE( [Chosen Sales MTD3],
    KEEPFILTERS(
        TOPN( 10, ALLSELECTED( 'Sales Data'[Customer Name] ), [Chosen Sales MTD3], DESC )
    )
)

_Rank Test =
    RANKX(
        ALLSELECTED('Sales Data'[Customer Name]),
        [Chosen Sales MTD3]
    )