Dynamic Selection with Dynamic Top N

[KMcCarthy9]

I have the below image as a page in my report. Would it be possible have end users choose the a specific KPI (MTTR Days, RT Days, FT Days, etc) via a slicer and have them select the Top/Bottom N number via a slicer? This new logic would allow me to remove all the different buttons and utimately just have 2 slicers, one with the KPIs listed and one with the Top N (10, 20, 50, etc) - maybe a third for Bottom N if it can't be done in 1. 

Possible? 
Thanks!

[Anonymous]

Hi @KMcCarthy9 ,

I have built data sample:

Assume:

FT = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Suppliers]))

RT = CALCULATE(AVERAGE('Table'[Value]),ALLEXCEPT('Table','Table'[Suppliers])) 

SDR = CALCULATE(MEDIAN('Table'[Value]),ALLEXCEPT('Table','Table'[Suppliers]))

Then please follow these steps:

1. Enter a Type table, the values are all of your measure names:

2. Create a measure to match the type name and measure like this:

Measure = SWITCH(MAX('Type'[Type]),"FT",[FT],"RT",[RT],"SDR",[SDR])

3. Add a what-if paremeter:

4. Create a flag measure for filter later:

Flag = 
var _min=MIN('TopN'[TopN])
var _max=MAX('TopN'[TopN])
var _rank=RANKX(ALL('Table'),CALCULATE ( [Measure] ),,DESC,Dense)
return IF(_rank>=_min && _rank<=_max,1,0)

5. Finally create a matrix visual as shown below:

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KMcCarthy9]

Thank you for this @Anonymous as it is very interesting! 

I can't quite get the TopN selector to work properly. 

Also, would it be possible to have the end user select one of the 'types' from a slicer and the Top N number and have the matrix still show all the data in the other type columns but only show the Top N of the 'type' that is selected. 

For example, the output for Top 3 for FT would look like this:

I have attached a sample pbix file here: pbix file 

Thank you for your help!

[KMcCarthy9]

Hi @amitchandak, thank you for this. This gets me halfway there as I got it working for 1 of the KPIs.
I'm trying to figure out how to expand up on this and have the end user be able to choose the KPI (MTTR, RT, FT, etc) from a similar slicer, and also have a slicer to select the Top N, but have them all housed in 1 table visual like above. 

Somthing like this:

[amitchandak]

@KMcCarthy9 , You can use what if for that

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...