dynamic top n

[eliasayyy]

hello i have 2 tables topn

and sales which has 2 columns product and sales

i want to make a slicer that has topn values in it and i want to make a bar chart for product and sale

i want when i choose top 10 , to see top 10 products when i select all , i want to see all products

PS. Exclude products whos sum is 0 or blank

[ValtteriN · Accepted solution]

Hi,

Here is a simplified example on how to do this. Data:

Measure:

Dynamic top N =

var _sel =MAX('Table (37)'[id]) //selected top N

var _rank = RANKX(ALLSELECTED('Table (36)'),CALCULATE(SUM('Table (36)'[Sales]))) //calculate sales rank CALCULATE is used to force row context

return

SWITCH(_sel,

1,IF(_rank<=5,1,0), //top 5

2,IF(_rank<=10,1,0), //top 10

3,IF(_rank<=15,1,0), //top 15

4, 1) //all

Place this measure as a filter:
Now the filter will work:

To remove blanks and 0 just use this as a filter:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/