My budget and actuals occur in different levels. I am using Pivot Tables in Excel to load this from a 2005 SSAS cube & display these figures.
The problem is, when I go the leaf level (lowest for actual and under leaf for Budget) I get the correct Actual but the budget replicates the parent value.
I have a Sales Budget which has been defined at a geographical State level, but the Sales Actuals occur at the geographical city level.
I've built a cube with a Geography dimension: Country - State - City
Measures: Actual, Budget
when I use Excel 2007 to browse the cube via a Pivot Table and I drill-down to State (New York) it correctly shows me
Actual: 10,000 Budget: 20,000
But If I drill down one more level I will see for Actuals: Princeton: 6,000 New Jersey: 4,000
and for the budget I will see both Princeton and New Jersey presenting 20,000.
How can I replace the Budget value with N/A or at least NULL or zero?
View Complete Post