Using DAX, how can I select data based on top N [total value] of a matrix?

[johnyip]

Hello all,

https://drive.google.com/file/d/1HM7YV-8SHFL02lkQaL7CnieF1NehozIA/view?usp=share_link

I have a question to ask, this seems simple but sadly I cannot quite handle that.

I have a matrix like something as shown below:

The matrix shows the monthly sales of each RETAILER, and the total sales for each RETAILER is calculated by the matrix automatically.

What I want to achieve is, to select only top 2 (actually top N because I may need to make it dynamic using slicer and DAX) RETAILER, based on the total YTD sales, up to the selected year-month, for each BUSINESS and RETAILER.

For example, the top 2 retailer for business A is Retailer 1 and Retailer 10 for March (circled in red). I want to show these two retailers only for the report of March for business A. The selections of the retailers for each business are expected to be changed every month since the ranking of YTD total sales for each retailers of each month for each business is expected to get changed.

The granularity of the matrix must be like as shown and cannot be changed. If the granularity can be reduced to only showing BUSINESS and RETAILER, I can solve that but this is not the case.

Would be apprecaited if anyone can show me how to achieve that using DAX. If there are any possible means that allows flexibility occur (top N and selectedyear-month), please feel free to do the knowledge sharing. Thanks a lot.

[Mikelytics]

HI @johnyip ,

Did you check the TOPN function?

TOPN function (DAX) - DAX | Microsoft Learn

You could make the first argument dynamic using a numeric parameter:

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

[johnyip]

Can everyone demostrate how I can use this function to achieve my goal? Sorry for asking to have the answer directly, but I am still on my way learning DAX, and there are so much things I need to pick up .