8 Replies Latest reply: Jun 2, 2014 2:54 AM by Tresesco B

# Pivot table avrage calculation

Hi Folks ,

I'm using pivot table as mentioned below:

Here subtotal at the bottom is is incorrect , I was looking for option in my table as below for Total mode please see below . but looks like it is disabled . Kindly help me how to get correct total or average at bottom .

Thanks,

AS

• ###### Re: Pivot table avrage calculation

Change your expression to something like:

=Sum(Aggr(Avg(AmtField) , Dimension))

• ###### Re: Pivot table avrage calculation

Hi Tresesco,

I know this option , but my calculated expressions are some how big like below:

if(([Available time]-[Downtime Planned])=0, num(0, '#,#0%'),

([Available time]-[Downtime Planned]-[Downtime Unplanned])

/

([Available time]-[Downtime Planned])

)

Is there any easy way as we have it straight table to select automatically as Sum  or Average etc...

Thanks,

AS

• ###### Re: Pivot table avrage calculation

In a pivot, I don't see an easier way. However, your if(..) expression too can be used similarly as I mentioned above.

• ###### Re: Re: Pivot table avrage calculation

Hi Tresesco,

Kindly see the attachment.

Please help me for Availability , Performance , Quality and OEE  total sum  calculation at the bottom.

Thanks,

AS

• ###### Re: Pivot table avrage calculation

HI,

What is your expected values for the total?  Sum of all rows?

Regards,

Jagan.

• ###### Re: Pivot table avrage calculation

Hi Jagan,

Yes it should be sum of all rows. It is coming perfect for all calculated expression except last 4 in pivot table.

Thanks,

AS

• ###### Re: Re: Re: Pivot table avrage calculation

Amit,

With aggr() the other expression label/column reference would not work. Use variables to declare the expressions and use them in Aggr() like I did in attached qvw. I did it for 'Availability'.

• ###### Re: Pivot table avrage calculation

Hi AS,

If your current selection is as shown below. Then what should be your answer for Availability , Performance , Quality and OEE.

Regards

Garry