Is there a way to replicate the Pivot Table calculated items within power query

[Ed Kelly]

Is there a way to replicate the calculated items within power query (can do it with calculated fields using custom columns however getting stuck with calculated items..think it has to be something to do with lists...) Trying to sidestep the error message that comes up when attempting to add a second value within Q4 of a pivot table once I use calculated items say for, running totals or % difference etc.. Attached is a simple Profit and Loss

P&L no cal items https://www.screencast.com/t/4PqyKlv5ExyQ
P&L with Calc items https://www.screencast.com/t/9tePNSS2r
P&L with Calc items and error message when attempting to add a 2nd value in Q4 https://www.screencast.com/t/tjpEmjnOa2

 

[Ed Kelly]

Is there a way to replicate the calculated items within power query (can do it with calculated fields using custom columns however getting stuck with calculated items..think it has to be something to do with lists...) Trying to sidestep the error message that comes up when attempting to add a second value within Q4 of a pivot table once I use calculated items say for, running totals or % difference etc.. Attached is a simple Profit and Loss

P&L no cal items https://www.screencast.com/t/4PqyKlv5ExyQ
P&L with Calc items https://www.screencast.com/t/9tePNSS2r
P&L with Calc items and error message when attempting to add a 2nd value in Q4 https://www.screencast.com/t/tjpEmjnOa2

Note the calculated items are Gross Profit and Net Profit

 

[Ken Puls]

Hi Ed,

Why not just add another column to your Pivot called "Gross Profit" based on conditional logic. Basically if you have Revenue or COGS, then it's Gross Profit, else "Other Expenses". You could then just use the field in a Pivot to create what you need.

 

[Ed Kelly]

Nice one Ken, thanks very much only getting a chance to look at it now.