TOPN (List of Top Ten Products)

[MarkCBB]

Hi there PBI Pros,

I need some help getting the Top 10 products by sales.

My Product names are in a Table called MAIN, my Sales Column is in a Table called VALUES
i.e. MAIN[PRODUCTS] and VALUES[SALES]

I would like to create a DAX Function to get the list.
I am using this in Power BI Desktop.

The Result I am looking to achieve is to have a column chart with the names of the Top 10 Products with the Value being the SALES.

Pulling out my hair, help.

 

[Matt Allington]

You need to take a step back from the Excel world and think in DAX. In Excel you could create a pivot and put a topn filter on the pivot table. No such feature currently exists in Power BI. So the question is, How can you do this in DAX? There're are a few approaches you could try, depending on what you need. If it is top 10, and always top 10, then you could try this.
Break the problem into pieces.
You could create a rankx measure to rank each product from 1 to n. Create a measure that does that and stick it in a visualisation. Each product will have a number. It is helpful to "see" the interim results in a visualisation as it helps you work through the problem. Then you could filter with an if statement to on,y keep the top 10. Something like this

top 10 sales = if ([my rankx measure] <= 10, sum(values[sales]))
put this new measure in your visualisation and remove the rankx, and it should give you what you want.

another approach is is you could put a rankx column in the product table and use it to filter your chart via slicer. The problem with this is it won't respond to different time periods.

 

[MarkCBB]

Hello Matt,

Firstly thank you for the help. (I have been playing around with DAX for a few days now, still struggling with the basics).

By following your steps, I was able to get it done. It was fun. I did a few manual Steps to confirm the results - all good.

RankedSites = RANKX(ALL(MAIN[SITE NAME]),CALCULATE(SUMX(RELATEDTABLE('VALUES'),'VALUES'[AMOUNT]),'VALUES'[VALUE TYPE]="VALUE",'VALUES'[YEAR]=YEAR(TODAY())),CALCULATE(SUM('VALUES'[AMOUNT]),'VALUES'[VALUE TYPE]="VALUE",'VALUES'[YEAR]=YEAR(TODAY())),DESC)

Top10Sites = IF([RankedSites]<=10,CALCULATE(SUM('VALUES'[AMOUNT]),'VALUES'[VALUE TYPE]="VALUE",'VALUES'[YEAR]=YEAR(TODAY())))

Thank you again