Pivot table totals functions - how is it calculated?

gal_polak — Sat, 16 Nov 2024 04:50:30 GMT

Hi all,

We use the pivot table in multiple sheets with different dimensions and measures, and we always encounter the same issue.

the total values are not making any sense,

i understand that when we use very complex set analysis in the measures it can be the cause,

but basically what i would like to have is like in a simple table, the ability to select the total function,

is there a way?

for example we have the following pivot table (top table)

and what we see is that for the Org. unit total it is not a simple average

when we convert to simple table (bottom table) and select "total function" AVG - only then the correct value is displayed.

my measure is this:

avg(EventValue)/3

can some one explain how is the pivot table totals are being calculated? or how can i force a function like average?

Re: Pivot table totals functions - how is it calculated?

Arthur_Fong — Thu, 29 Aug 2019 00:31:46 GMT

First, create a new measure called Dimensionality().

You will notice there will be a different value assigned to total and non-total rows:

You can control the expression for the Total rows by using conditional if..else

Eg: if(Dimensionality()=1, avg(Number),sum(Number))

Refer attached qvf for reference.

Thanks and regards,

Arthur Fong

Refer