J
Josh Smith
I've seen this question asked before, but I haven't seen an adequate
answer, so I figured I'd try to ask it again to see if there are any
fresh ideas on it:
Take an example where you've got raw data of orders that contains the
following fields
Region
City
Product
Total Sales $
You create a pivot table With Region & City as the Row Fields, Product
as the Column Field and Total Sales $ as the data. This works fine.
Now if you try to add a calculated item, say the difference in sales
of Product A and Product B, the whole pivot blows up and you get every
city appearing in every region.
I understand that, to an extent, this example violates the purpose of
the pivot table since the Region & City fields really outline data
rather than truly pivot off of one another, but I feel like there
should be a way to make this work. The initial state works because
the field settings on City have the "Show items with no data" checkbox
unselected. However, the calculated item now provides data in the
form of a formula for every possible Region/City combo. Does anyone
know of a way to construct the Calculated Item so that it still
registers as 'null data' and keeps the invalid region/city combos
hidden. Any help with this would be greatly appreciated.
Thanks
answer, so I figured I'd try to ask it again to see if there are any
fresh ideas on it:
Take an example where you've got raw data of orders that contains the
following fields
Region
City
Product
Total Sales $
You create a pivot table With Region & City as the Row Fields, Product
as the Column Field and Total Sales $ as the data. This works fine.
Now if you try to add a calculated item, say the difference in sales
of Product A and Product B, the whole pivot blows up and you get every
city appearing in every region.
I understand that, to an extent, this example violates the purpose of
the pivot table since the Region & City fields really outline data
rather than truly pivot off of one another, but I feel like there
should be a way to make this work. The initial state works because
the field settings on City have the "Show items with no data" checkbox
unselected. However, the calculated item now provides data in the
form of a formula for every possible Region/City combo. Does anyone
know of a way to construct the Calculated Item so that it still
registers as 'null data' and keeps the invalid region/city combos
hidden. Any help with this would be greatly appreciated.
Thanks