Average grand total instead of Sum in pivot table

[raju_salmon]

Hi Experts,

Gone through some of threads however not solved the issue. Unable to share app due to confidentiality.

I have a pivot table in my app something like below, whereas i need to replace red number (Average) at yellow highlighted (Sum).

I have three dimensions and one expression (Ex: Sum(No)).

Is there anyway to do it by using dimensionality() or any other way?

Regards,

Raju.

[sunny_talwar]

Have you tried using Avg(No)?

[raju_salmon]

Hi Sunny,

That will give Average values for expression.. whereas i need overall average at Totals.

I used show partial sum in presentation tab for totals, whereas i need average there.

[sunny_talwar]

Would you be able to share your sample excel file you have above? I know this can be done with Aggr() function. But the exact expression will need to be tested

[raju_salmon]

May i know where is the option to attach the excel. If you do not mind...

[sunny_talwar]

Step 1:

Step 2:

[sunny_talwar · Accepted solution]

Try this:

=Avg(Aggr(If(Sum(Value) > 0, Sum(Value)), [Channel Name], PRODUCT_NAME, MONTH))

Replace Sum(Value) with your expression

Attaching a sample

[raju_salmon]

Thank you Sunny.

[sunny_talwar]

No problem. I am glad I was able to help.

Best,

Sunny