Chandoo, at the Pointy Haired Dilbert blog, has posted 5 Excel Pivot Table tricks today. His blog posts are always informative and entertaining, so go and take a look.
The 5 Pivot Table tricks are:
- Drill Down Pivot Tables
- Change Summary from Total
- Slice and Dice Pivots
- Difference from Last Month
- Calculated Fields in Pivots
Did you know all those pivot table tricks? Learn anything new?
______________
For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.
______________
Hello Debra
I am using Calculated Item (Not a Calculated Field) in a Pivot table
to calculate variance in Budget and Actual figures. Though this is
working fine and I am getting the variance calculation properly.
However the page filter is not showing proper results.
Once I am using page fileter to see data for a particular geography,
it keep on showing the claculated items for data which is not related
to geography as per page filter.
My file is available at this link , I am not sure of the
mistake I am making in this Pivot Table
In this file Pivot table is filtered on Geography “East” , however the
report is still showing the calculated item “Variance” for states that
does not belong to geography “East”
Regards//Yogesh Gupta
Hi Yogesh,
Unfortunately this is one of the results of using Calculated Items and there’s no way to turn it off. If you’re using Excel 2007, you can filter the Row fields so they don’t show amounts equal to zero. That would hide all the extra items that appear after you create a calculated field.
If you’re using an earlier version of Excel, try a ‘Difference From’ custom calculation, instead of a calculated item.