Forum Discussion

JemmaD's avatar
JemmaD
Helper V
2 years ago
Solved

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 ...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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.

rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

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.