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

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