Pivot Table Totals not showing unless fully expanded

[#1]

Dear all,

I have a question regarding the Pivot table totals. I know that there are already several discussions about this topic but so far I have not found a proper solution.

I have a Pivot table with 4 levels, on each level I show the totals. This works for my first 3 expressions but not always for the other ones.

The set-up of my Pivot table:

4 dimensions=

DIM1

DIM2

DIM3

DIM4

All my expressions have the following structure:

Expr=if(DIM3='ABCD',[Test cases assessed],count([# of tc performed]))

So basically I want to display a different value if DIM3 equals ABCD in the other case the count needs to be performed. If I fully expand my pivot table the fields are correctly calculated and QV is always able to calculate the count (if applicable) the problem is that I don't get the total values in case DIM3=ABCD.

Does anyone have an idea to what this might be related? Could it be a formatting issue of my data? Or is there something wrong in my formula?

Thanks in advance.

Kind regards,

Jens

[Jason_Michaelides]

Hi Jens,

Count() is an aggregation so will display a total.  Where DIM3='ABCD' you are just displaying a different field and there is no aggregation to perform, so the totals will not work.  Try this as your expression instead:

=if(DIM3='ABCD',Sum([Test cases assessed]),count([# of tc performed]))

Hope this helps,

Jason

[jonathandienst · Accepted solution]

Jens

I think the problem is that DIM3 does not have a defined value at the higher total levels, so your expression returns null. The solution is an aggr expression, like this:

  Sum(Aggr(If(DIM3='ABCD',[Test cases assessed],count([# of tc performed])), DIM1, DIM2, DIM3, DIM4))

Hope that helps

Jonathan

[Jason_Michaelides]

D'oh! My brain's not working this morning... Nice one Jonathan

[#5]

Thanks Jonathan for your solution. It is working fine!

Maybe a small additional question: what if I want the total of precentages? If I use Sum(aggr()). It gives off course a wrong number.

Does anyone know what might be the correct way to calculate this?

[jonathandienst]

Jens

You can calculate a ratio or percentage by including the ratio inside the sum (if sum of rows makes sense), or do the ratio of two Aggr expressions (which is the calculation applied across the data set).

Does that help to answer your question?

Jonathan

[#7]

Hi Jonathan,

This indeed solved the issue.

Thanks!