Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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