I have a calculated field (formula is highlighted yellow in the screen shot below).
It looks a bit complex but it's just a way of producing a result which is RateA*QtyA unless QtyB is not zero.
In this case the result is RateB*QtyB.
The formula works fine for each item "Name" but the subtotals don't work for the groups "Power" if QtyB is not zero.
I can see that where QtyB IS zero the subtotal works out as the of RateA*QtyA*Count(Power) - this I can sort of understand but is still not what I want.
My understanding of how column totals work is clearly where my problem is.
Which then leads to how do I get my averages of the "power" groups.
I would ideally like to do this all in the PivotTable.
I have attached the simple example spreadsheet if that helps.
I have struggle with this for days. Help please!
Capture.JPG
Bookmarks