Forum Discussion
Creating a measure with Top x values
Hi PBI Gang 🙂
I have a matrix visual which shows the actual premiums, target premiums and variance between the amounts by different broker groups.
eg.
Broker Actual Target Variance
ABC 50 100 -0.5%
XYZ 250 80 2.1% ...etc (Actual, Target & Variance are all measures)
I now need to take the Top 100 Actual amounts and from those amounts create a visual that only displays the Top 20 Brokers with a Variance <= -0.5%.
So the matrix format will stay the same (Broker, Actual, Target, Variance) but needs to be filtered for the Top 20 variances that are <= -0.5% from the pool of the Top 100 Actual amounts. I hope this make sense
I have no idea how to go about it and will really appreciate any assistance!
Many many thanks
1 Reply
- johnt75
Super User
You could try a new measure like
Variance ( Top 100 ) = CALCULATE( [Variance], TOPN(100, 'Table', 'Table'[Actual]) )and then use this new measure as the parameter for a TOPN visual filter on your matrix