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 number and percentage. Is it possible to apply this purpose using unpivot column in power query?

 

Here is the data matrix example:

I would like to convert (unpivot) and visualize the table into this:

Kindly share your thoughts on this. 

 

Thank you in advance!

 

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

3 Replies

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Amit, 

      Thanks for sharing your thought. However the steps is still not achieve what I need to perform different calculation type per-each category/indicator As you may see in the scond table.