Forum Discussion

HB13's avatar
HB13
Icon for Helper I rankHelper I
4 years ago

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

  • 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