Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Unpivot Column with Different Calculation Applied

Hi there,   I am trying to unpivot data matrix using pwoer query but the data calculation is not only using summraize but also average since the value have different value format, iie. absolute num...
Calculations
unpivot
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    Please try

    Total Target (Switch) =
IF (
    CONTAINSSTRING ( MAX ( 'Table'[Indicator] ), "%" ),
    AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [Total Target] ),
    SUMX ( VALUES ( 'Date'[YearMonth] ), [Total Target] )
)

    Same for total actual

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Anonymous 
Please try

Total Target (Switch) =
IF (
    CONTAINSSTRING ( MAX ( 'Table'[Indicator] ), "%" ),
    AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [Total Target] ),
    SUMX ( VALUES ( 'Date'[YearMonth] ), [Total Target] )
)

Same for total actual