Forum Discussion

eliasayyy's avatar
eliasayyy
Icon for Memorable Member rankMemorable Member
3 years ago
Solved

dynamic top n

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 ...
  • ValtteriN's avatar
    ValtteriN
    3 years ago

    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/
ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
3 years ago

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/