Log In
Register
Home
Dashboards
Power Query
Power Pivot
Power BI
Excel Expert
May 6, 2022
Offline
Hello again.
I have a pivot table with values every 30 min over 6 channels (AC1, AC2,AC3, PV1,PV2,PV3).
I need to create a column that shows the formula (AC1+AC2+AC3)-(PV1+PV2+PV3) for each 30 min interval over 12 months of data. Once I have this column I wiould then like to find the Max value of that column per month.
What's the best way to do this? I looked at the calculated field dialog box but it wants to do the formula from the source data whereas this formula needs to come from a drill down of the source data.
The source file is too big to attach but I've added a screen shot. For example: (B9+C9+D9)-(E9+F9+G9) = value in new column
Thank you for your help.
See Answer
Trusted Members
February 13, 2021
Offline
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.
July 16, 2010
Offline
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
Dashboards
Power Query
Power Pivot
Power BI
Excel Expert
May 6, 2022
Offline
Hi Mynda,
How would I do this in PQ?
Trusted Members
February 13, 2021
Offline
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.
July 16, 2010
Offline
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
Answers Post
1 Guest(s)
