Top N Filtering

Resolved · High Priority · Version 2016

Tue 12 May 2020, 21:42 Delegate Rachel said...

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

Wed 13 May 2020, 12:18 Trainer Jens said...

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

Wed 13 May 2020, 15:52 Edited on Wed 13 May 2020, 15:53 Trainer Jens said...

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

Training courses

	98.8 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Power BI tip:

Create Interactive Dashboards

Build interactive dashboards by leveraging features like slicers, drill-through, and bookmarks. Slicers allow users to filter data dynamically, while drill-through enables detailed exploration of specific data points. Bookmarks help you save the current view, making it easy to switch between different states of your report.

View all Power BI hints and tips