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 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'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/

1 Reply

  • 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/