Pivot table avrage calculation

[amit_saini]

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

[tresesco]

Change your expression to something like:

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

[amit_saini]

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

[tresesco]

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

[amit_saini]

Hi Tresesco,

Kindly see the attachment.

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

Thanks,

AS

[#6]

Hi AS,

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

Regards

Garry

[jagan]

HI,

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

Regards,

Jagan.

[amit_saini]

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

[tresesco]

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'.