Rachel has attended:
Power BI Modelling, Visualisation and Publishing course
Top N Filtering
Hello,
I have been building a dashboard and created a Top N filter which works for multiple charts however, I am trying to count the number of “Lease IDs” which fall into three distinct categories, using the Top 10 example, I have the following table
Under | Neutral | Over
10 | 10 | 10
As you can see there are 3 categories currently bringing back 10 distinct Lease IDs each, I would ideally like this to show a total of 10 across the 3 categories (it should read 7 Under, 2 Neutral and 1 Over).
This is my current measure that I am using for the values in the table
LeaseCount Top N Leases = CALCULATE([Total Lease Count], TOPN([SelectedTopNValue],'Portfolio Data Set', [Total Lease Count], DESC, 'Portfolio Data Set'[Lease ID]))
Where
Total Lease Count = COUNTROWS('Portfolio Data Set')
SelectedTopNValue = IF(HASONEVALUE('Top N Table'[TopN Names]), VALUES('Top N Table'[TopN Values]), AVERAGEX(VALUES('Portfolio Data Set'[Lease ID]),[Total Rent]))
Top N Table is;
TopN Values | TopN Names
10 | Top 10
25 | Top 25
75 | Top 75
Thanks,
Rachel
RE: Top N Filtering
Hi Rachel,
Thank you for the forum question.
I replicated what you have done in one of my data models. My logic told me that if I just added the measure to a matrix and the categories it would return 5, 3, 2 (10 in total), but I as you just got 10,10,10. I will have to think, but I will come back to you today.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Top N Filtering
Hi Rachel,
I have found a way to do it but I am not sure it is the most efficient.
Make a measure for each category Under, Neutral, Over. Just copy the maesure:
LeaseCount Top N Leases = CALCULATE([Total Lease Count], TOPN([SelectedTopNValue],'Portfolio Data Set', [Total Lease Count], DESC, 'Portfolio Data Set'[Lease ID]))
Three times and add a Filter function, which filter the category.
Try:
LeaseCount Top N Leases Under = CALCULATE([Total Lease Count], TOPN([SelectedTopNValue],'Portfolio Data Set', [Total Lease Count], DESC, 'Portfolio Data Set'[Lease ID]),Filter('Portfolio Data Set','Portfolio Data Set'["the column name where the categories are listed"]="Under"))
Do the same for Neutral and Over.
Unfortunately you cannot display this in a table but if you add three Card visuals and add one of the three measures to each of the cards it should work.
It is the best solution I could come up with and I hope everything makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
