Hello,
I have been facing a problem in displaying values that are already aggregated at each level in a hierarchy. for example-
I have a hierarchy table-
BA, PC1, PC2, PC3, PCID <-- (columns)
A, 1
A, B 2
A, B, C, 3
A, B, P 10
X, 9
X, D, 4
X, D, E, 5
X, D, E, F, 6
And now there is a fact table which has data something like this-
PCID, Amount, Year, Month, Scenario
1, 5000, 2020, Jan, Actual
1, 4000, 2020, Feb, Actual
2, 8000, 2020, Jan, Actual
2, 800, 2020, Feb, Actual
3, 1500, 2020, Jan, Actual
3, 1200, 2020, Feb, Actual
4, 6500, 2020, Jan, Actual
5, 5900, 2020, Jan, Actual
6, 5500, 2020, Jan, Actual
9, 7000, 2020, Jan, Actual
10, 2200, 2020, Jan, Actual
Now when I use these columns from the hierarchy table as dimensions BA, PC1, PC2 and PC3. The amount for Business Area(BA) A amount should be 9000 i.e. summing up the first two rows in the fact table whereas for X it should be 4000.
Basically, we have the amount aggregated at each level already so we need not sum up the last PC's amount of their parents from the hierarchy table. Also, it's a constraint that I have to show the aggregated values given to us in the fact table.
And as we keep expanding the pivot table (+) the respective aggregated values for PCs should get summed up.
Issues which I'm facing-
while doing the sum of amount for the year 2020, for Business Area A the sum is coming as the sum of amount for A, B, and C as B and C comes under A. and same is the case when I expand A the amount for B is coming as sum of B and C. Kindly help me to understand how to handle such data in Pivot table.
Expected Result - (When Pivot table is fully expanded)
BA PC1 PC2 PC3 Amount
A 9000
A B 8800
A B C 2700
A B P 2200
X 7000
X D 6500
X D E 5900
X D E F 5500
