Measure not showing values when drilling down in a pivot table

asantos — Sat, 16 Nov 2024 01:35:51 GMT

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,

Re: Measure not showing values when drilling down in a pivot table

sunny_talwar — Tue, 25 Aug 2020 14:19:11 GMT

It will be easy if you are able to share a sample where we can see the issue

Re: Measure not showing values when drilling down in a pivot table

asantos — Tue, 25 Aug 2020 14:27:41 GMT

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.

topic Re: Measure not showing values when drilling down in a pivot table in App Development

Measure not showing values when drilling down in a pivot table

Re: Measure not showing values when drilling down in a pivot table

Re: Measure not showing values when drilling down in a pivot table