TOPN

[Maghed]

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 

[Ashish_Mathur]

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?

[Maghed]

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

[ImkeF]

This is newest dynamic TOPN-approach: https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

[parry2k]

@Ashish_Mathur I'm afraid it will work.

[parry2k]

@Maghed

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

[Maghed]

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]

@Maghed 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