Create a TOPN on complex measure

[JemmaD]

I have a measure which calculates variance between two rows of data based on a field called [input].

I want to add a second measure which shows me the top 5 highest variance only and I can't figure out how and if I can insert a TOPN function into the measure.

The existing measure is below, can you please help me with syntax on adding a top 5 variance?

Measure = 
IF ( HASONEVALUE( Results[input] ), CALCULATE ( SUM ( 'Results'[gwp] ) ), MAXX ( VALUES ( Results[input] ), CALCULATE ( SUM ( Results[gwp] ) ) ) - MINX ( VALUES ( Results[input] ), CALCULATE ( SUM ( Results[gwp] ) ) ) )

[rajendraongole1 · Accepted solution]

Hi @JemmaD - Hope you already have a measure that calculates the variance to use this in top 5

Variance Measure =

IF (
HASONEVALUE( Results[input] ),
CALCULATE ( SUM ( 'Results'[gwp] ) ),
MAXX ( VALUES ( Results[input] ), CALCULATE ( SUM ( Results[gwp] ) ) ) -
MINX ( VALUES ( Results[input] ), CALCULATE ( SUM ( Results[gwp] ) ) )
)

create a new measure to calculate and return the sum of the top 5 highest variance

Top 5 Variance =
SUMX (
TOPN (
5,
SUMMARIZE (
'Results',
Results[input],
"Variance", [Variance Measure]
),
[Variance Measure],
DESC
),
[Variance Measure]
)

Hope this helps.