Dynamic TopN with RankX

[rfernandes]

Hello everyone,

I have a base with a lot of products, and i want to show the sales of each product along the time in a line chart.  To make the chart more clean, i want to make a dynamic top filter. I used this tutorial : Dynamic TopN made easy

This is the current formula:

TopN_Sales = 
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN]) // 'TopN'[TopN] is the table with the slicer
RETURN
IF(RANKX(ALL(base[product]);[Tot_Sales])<=SelectedTop;[Tot_Sales];BLANK()) // Tot_Sales just sum all sales

The way it is working, it filters the TopN of each month.  If i filter Top 4, the chart will show 5 products and ll have some break between the lines.

If the topN are the same across the whole period, the dynamic filter works fine.

I want to make a dynamic topN filter that works in the same way as the TopN filter in visualization. Is there a way to rank by the sum of total sales?

 Here is a pbix : Example.pbix

Thank you!

[Anonymous]

Hi @rfernandes,

The Visual you've created does exactly what it needs to be done.

It shows the top from that month that's why you'll get blank rows in between because Blue is ranked 4th on may june july and august and orange is ranked 4th on the other months.

[rfernandes]

Yes.. I know how it is working, but the question is how to make it work different.

[Anonymous · Accepted solution]

It is unclear on what you mean with "make it work different".

Are you trying to show the 4 items with the highest total sales over a year? or just during the month?

EDIT: 

If took your example file and created the following measure:

SubTotal = CALCULATE(SUM(Base[Valor Total]);ALLEXCEPT(Base;Base[marca]))

I then changed your TopN Measure to:

TopN_total_sales = 
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
var RankMarca = RANKX(ALL(Base[marca]);[SubTotal])

RETURN
IF(RANKX(ALL(Base[marca]);[SubTotal])<=SelectedTop;[total_sales];BLANK())

I'll get this as a result:

Result

I hope that this solves your problem!

Kind Regards.

PS: If it does solve your problem please mark this as answer.

[rfernandes]

Thank you , it worked perfectly!

In the TopN Measure, the var RankMarca is not used, so i deleted it. And [GrandTotal] should be [SubTotal], right?

Thanks

[v-yulgu-msft]

Hi @rfernandes,

Why is the highlighted value different?

How is the data in source table like? What is the formula of [Tol_Sales]? You said "Tol Sales" sums all sales, but why did you compare it with TopN selection?

Best regards,

Yuliana Gu

[rfernandes]

Sry, it was a typo. I added the original post some prints and a pbix file.

[themistoklis]

@rfernandes

Have you tried adding the month, the product and measure in a matrix as is and then on the filter pane select the top2.

Measure = tablename[Total Sales]

See image below:

[rfernandes]

Yes, i want to make it work just like the TopN filter, but i want to make it dynamic.