Forum Discussion
Create a TOPN on complex measure
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] ) ) ) )
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.
1 Reply
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.