Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
It will be easy if you are able to share a sample where we can see the issue
Hi, @sunny_talwar ! Thanks for your response!
I wish there'd be an easy way, but I don't have any way to download the sheet/file + the data is confidential.
I'm not working on the desktop version...
I'll add how the 2020-Var is created within the script, if that helps think of where the flaw sits.