Forum Discussion
How to select Top/Bottom N results dynamically
I have a relatively simple data set that looks something along the lines of this:
Id TestName Grade RunTime
| 1 | Test1 | Okay | 74 |
| 2 | Test2 | Poor | 24 |
| 3 | Test3 | Okay | 15 |
| 4 | Test4 | Okay | 33 |
| 5 | Test5 | Poor | 93 |
| 6 | Test6 | Good | 61 |
| 7 | Test7 | Great | 50 |
| 8 | Test8 | Okay | 200 |
| 9 | Test9 | Great | 10 |
| 222 | Test222 | Poor | 184 |
| 223 | Test223 | Poor | 101 |
| 224 | Test224 | Poor | 101 |
| 225 | Test225 | Poor | 100 |
| 226 | Test226 | Poor | 99 |
| 227 | Test227 | Poor | 101 |
Additionally I have two helper tables to use as slicer values for the top N options and to select Top/Bottom.
I have a chart to show the Top/Bottom N tests using their RunTime as value to determine that logic. Additionally I have a dounut chart to show the percentage of grades in all my table. I'm using a way to use measures to be able to dynamically change the TOP/BOTTOM options to see.
TopN Grades by TestName =
// Var to hold the value of the slicer
VAR SelectedTop = SELECTEDVALUE('Selected Top N'[Top N])
// Assign a rank to all controls based upon total visit count.
VAR RankValue = if(SELECTEDVALUE('Top or Bottom'[Option])=="Top",
RANKX(ALLSELECTED(TestResults[TestName]),[Total Grades Count],,DESC, Dense)<= SelectedTop, // All ranks that are less than or equal to my slicer value are returned.
RANKX(ALLSELECTED(TestResults[TestName]),[Total Grades Count],,ASC, Dense)<= SelectedTop) // All ranks that are less than or equal to my slicer value are returned.
RETURN
SWITCH(TRUE(),
SelectedTop = 0,[Total Grades Count],
RankValue,
// Return all values if Input value is unknown
[Total Grades Count]
)
When I select the Top option it all sort of works ( I added an extra table to try to debug what is going on):
And when I select the Bottom option my donut chart dissapears:
You can also see that the table I added does not repsect the Top N values selected, it always seems to show more than the selected value of 5 in this example. I can't figure out what I'm doing wrong and I hope someone can guide me in the right direction. Here is the link to the sample pbix file for this dataset: Sample PBI
Thanks!
2 Replies
- Ashish_MathurSuper User
Hi,
See if this gets you started. The Table and the bar chart works fine - not sure of the pie. My solution works for Top only (not for bottom). Download PBI file from here.
Hope this helps.
- amitchandakSuper User
ebecerra , Try TOPN for that
TopN Grades by TestName =
// Var to hold the value of the slicer
VAR SelectedTop = SELECTEDVALUE('Selected Top N'[Top N])// Assign a rank to all controls based upon total visit count.
VAR RankValue = if(SELECTEDVALUE('Top or Bottom'[Option])=="Top",
CALCULATE([Total Grades Count], TOPN(SelectedTop,ALL(TestResults [TestName]),[Total Grades Count],dense), VALUES(TestResults [TestName])),
CALCULATE([Total Grades Count], TOPN(SelectedTop,ALL(TestResults [TestName]),[Total Grades Count],asc), VALUES(TestResults [TestName]))
)
RETURN
RankValueTOPN: https://www.youtube.com/watch?v=QIVEFp-QiOk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42