Unpivot table with calculated DAX columns

[Anonymous]

Hi there,

I have a table that has many calculated DAX tables so I can't do this in powerquery, I guess i could extract the table into csv then unpivot it but it would seem like there is a better way to do this?

Below is a view of the table structure, I would like the Application Cost, Telesales cost etc to be stacked like accounts so that i can build a P&L. any suggestion on how to do this elegantly?

[v-chuncz-msft · Accepted solution]

@Anonymous 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/How-to-create-a-Pivot-table-on-calculated-column-Please-help-me/m-p/664505#M319528

[Anonymous]

You can use the unpivot function in Power Query, though you would probably be better served just creating measures with those values so  you dont have to load them, plus measures would be much more dynamic. 

[Anonymous]

Thanks but i can't use power query on dax calculated tables right?  If there is please show me!

[Anonymous]

Correct.  So I'd steer away from too many calculated columns and stick to creating measures for these figures. 

[Anonymous]

Thanks but that doesn't solve my issue as I need to unpivot this table to build a PandL.  Creating measures won't help me with the end goal of a PandL either.

[Anonymous]

Not sure I follow on why you feel you have to use calculated columns... Did a quick google search on p&l in power bi and came across this, maybe that could help you more than I can. 

https://www.youtube.com/watch?v=-jpYwz8-oYc

[Anonymous]

Thanks for trying to help but the video highlights exactly why i need to transform the calculated columns into accounts or "categories" (as highlighted in the video).