Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

TOPN

Hi All,

I spent 2 days trying do the following with no success!

I have a simple table "Ranking Table"  with one column "Rank" that has the following 4 values (3,5,7,10) 

I have also a visual slicer that allows me to select one fo the 4 values 3,5,7,10 from the above

then I have this formula: Table2 = TOPN(7,Table1,Table1[Dues],DESC)

this works perfect as long as I type 3 or 5 or 7 or 10 manually

But it never works if I select the value 3 or 5 or 7  or 10 from the slicer

I tried all the possible creating measures, varaibles etc and using instead of typing the number directly in TOPN but it never works 

Tips and Tricks

7 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    Anonymous

     

    You cannot create a table and pass slicer value to it. Any reason why you are creating a table?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I am new ..

      I need to create the table to be able to display it as visual

      It simply contains two columns Product and Quantity

      Table1 that I am using is well structured with unique product ID and sum of quantity for each

      \What I am trying to achieve is to get a dynamic number of rows from Table1 ranked decending to be able to dispaly as table visual

       

      meaning user selects 5 she gets a table with 5 top Products in terms of quantity

      user selects 7 she gets the same but for the TOP 7 not 5 and so on.

      Thank you.

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

        Anonymous no problem, understood. Please share data in excel using onedrive/google drive, remove any sensitive information before sharing and I will get you the solution

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    Try this

    1. Create a measure to harvest the value chosen in the slicer: Measure1 = MIN(Topn[Value])
    2. Create a messure to calculate Total dues.  Total dues = SUM(Table1[Dues])
    3. Modify your measure to Table2 = TOPN([Measure1],Table1,[Total dues],DESC)

    Does this work?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Ashish and All,

      No I tried this and will never work. Varaibles , measures etc.

      However i was able t find a workaround without creating table 2 at all.

       

      0- Table1 has only 2 columns GA & total Dues(Which I created a measure mTotal_Dues for it

      1- I created a measure   mRANK = RANKX(ALL(table1),[mTotal_Dues],,DESC) in Table1

      2- I created another measure mSelTopNNumber = INT(SELECTEDVALUE('Ranking Selection'[Rank])) still in Table 1 to get the selected value form my slicer 3,5,7,10 "The Slicer Table has only one cloumn Rank with 3,5,7,10 as values

      3-I created a 3rd measure check = IF([mRANK]<=[mSelTopNNumber],1,0) still in Table1 to test the rank of each row against the selected value in the slicer to Check measure in the Visual Filter and set its value to 1,0

       

      Now, every time I choose a value from the slicer, 3,5,7,10 my visual diplays only the n number of rows that meets the visual filter with value1 ...meaning if I selected 5 in the slicer, Visual diplays only 5 rows ranked corretly etc , selecting 7 visual diplays 7 rows and so on,

      I get rid of TOPN that seems static, meaning I can supply a number only in the n_Value paramter not any measure or variable which provide static visual , but not dynamic visual at all.

       

      If an expert has an alternative to get TOPN to allow dynamic value in n_value parameter, please proivde a way for this.

       

      BR

      Maghed