topic Re: Unpivot Column with Different Calculation Applied in DAX Commands and Tips

Unpivot Column with Different Calculation Applied

Anonymous — Tue, 04 Apr 2023 05:10:05 GMT

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!

Re: Unpivot Column with Different Calculation Applied

amitchandak — Tue, 04 Apr 2023 08:51:53 GMT

Anonymous , 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

Re: Unpivot Column with Different Calculation Applied

Anonymous — Tue, 04 Apr 2023 09:52:08 GMT

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.

Re: Unpivot Column with Different Calculation Applied

tamerj1 — Tue, 04 Apr 2023 10:30:12 GMT

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