I have a couple of thoughts: one is to do calculated cells that aren't part of the Pivot Table. I do think, though, that your best option would be to send your data to the data model and do a calculated measure. I am almost certain you can create a measure that takes into account the filters on the Pivot Table. Unfortunately, I'm still new to them and don't have the formula off the top of my head.

Hi Anita,

What you want to do is group AV together and PV together and then take one group from the other, but when you group items in regular PivotTables you cannot add calculated fields.

So, your options are to add a column to your source data that classifies the channels into AV or PV and then use that field in your PivotTable, then add your calculated field, or as Jessica suggested, use Power Pivot and write a DAX measure. With the regular PivotTable option, you won't see the individual channels, only the totals of the channel groups.

Mynda

Hi Mynda,

How would I do this in PQ?  

In PQ highlight your 3 AV click add column>in the group "From Number", Standard>Add. Do the same for your PV columns. Highlight your AV & PV columns and in that same dropdown would be a subtract. You can then delete your unneeded helper columns. Yes, there are other ways to do it, but that's using the built-in functions.

Hi Anita,

I think Jessica is picturing the data in PQ is in separate columns as it appears in your screenshot, but presumably you have one column called channel. If so, you can add a new custom column with an 'if' statement to determine if the channel column contains 'AC' or 'PV' and return the tag accordingly.

You can then use this new column in a PivotTable to summarise the kW Demand and add a calculated column to take one from the other.

Mynda

Thanks Mynda,

You are correct.  I have once column called 'channel'.  What you suggested has worked.

Anita.