Unpivot Column with Different Calculation Applied

[JuliusSugi]

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!

[tamerj1 · Accepted solution]

Hi @JuliusSugi 
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

[amitchandak]

@JuliusSugi , I am assuming the first one is the source

How to Deal with Double Headers in Power BI | Free Power BI Tutorials: https://youtu.be/1e16oHv9kTk

[JuliusSugi]

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.