Excel PivotTable: How do you calculate the difference between a two columns in a multileveled PivotTable?

So say I have a pretty simple PivotTable, with reach record having Month, Type, Lodging and Lodg. Tax.

My records are uniquely organized by Date and by Type (with Types being "Estimate" and "Real").

I want to be able to do things like calculate the difference between our estimates and our real costs. Is there a way to do that with PivotTables?

Ideally, it'd do something like determine a total difference instead of a total sum, as seen below (on the right).

8

1 Answer

You should be able to view a difference. What about this workaround:

1. Create a new count field: Click on Analyze card -> Fields, Items & Sets -> Count field
2. Name it e.g. Lodgingdiff and the formula would be

= Lodging

3. Then add the Lodgingdiff into the Values in the Pivot table
4. Go to Field settings -> Show Values As -> Choose Difference from, set Base field as "Type" and Base Item as e.g. "Estimate"

It add a new column into the Pivot Table with difference of real Lodging from estimated Lodging. You can see the difference bellow the column Real | Sum of Lodgingdiff. Best regards, Lukáš

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy