Hello!
There is this pivot table below, which, when we try drilling down on Variation Column, the calculated rows are empty:
This is the Pivot Table before drill-down:
| Description | 2020-Goal | 2020-Actual | 2020-Var |
| Revenue | 1000 | 900 | -100 |
| Volume | 10 | 9 | -1 |
| Unit Price | 100 | 80 | -20 |
This is how Unit Price row is calculated (measure):
if([Description]='Unit Price',
if(WildMatch([Fact],'*Var'),Above(Sum(Revenue),1)/Above(Sum(Volume),1) - Above(Sum(Revenue),2)/Above(Sum(Volume),2),
Sum(Revenue)/Sum(Volume))
When the user drills down on 2020-Goal or 2020-Actual, it shows the values by month and they are all correct. However, when the drill down is on 2020-Var, then Revenue and Volume are correct, but the calculated row (Unit Price) is empty.
Edited it to add the part of the script that creates the Fact 2020-Var:
VarTable:
Load
"Ano",
"Mês2",
"FACT",
[Value] as [Value GOAL],
[Volume] as [Volume GOAL],
[Unit Price] as [Unit Price GOAL]
Resident FirstTable
where WildMatch("Fact",'*GOAL*');
Concatenate (VarTable)
Load
"Ano",
"Mês2",
"FACT",
[Value] as [Value ACTUAL],
[Volume] as [Volume ACTUAL],
[Unit Price] as [Unit Price ACTUAL]
Resident FirstTable
where WildMatch("Fact",'*ACTUAL*');
Concatenate (FinalTable)
Load
"Ano",
"Mês2",
"Ano" & '-' & 'Variação' as "Fact",
Sum([Value ACTUAL]) - Sum([Value GOAL]) as "Value" ,
Sum([Volume ACTUAL]) - Sum([Volume GOAL]) as "Volume",
Sum([Revenue ACTUAL]) - Sum([Revenue GOAL]) as "Revenue",
Sum(Unit Price ACTUAL]) - Sum([Unit Price GOAL]) as [Unit Price]
Any idea as to how to tackle this one?
I appreciate your time!
Regards,
