Pivot Table Grand Totals and hiding one field

[vishsaggi]

Hello Experts,

Need an urgent help with this pivot table I have been scratching my brains off.

Really appreciate your help.

I have a pivot table which does not display Grand Total in the bottom, i tried using Partial sum with subtotals in the bottom but I am sure I am doing something wrong here, I am out of thoughts. Please advice....

Please let me know if I have not made it clear.

Many thanks
Vish.

[#2]

Hi Vish,

We cannot display grand total in pivot table. We  can only get partial Sums. If you need to display grand total you can use Straight table.

[johnw]

You're multiplying by BTU Value, which has multiple possible values, and is therefore null for the grand total. That makes the entire grand total row null, and therefore it doesn't display.

[vishsaggi]

Thanks John for your prompt reply. Is there any way where I can just do an inner join and convert nulls to 0's and then display Grand Totals?

Appreciate your help.

[Anil_Babu_Samineni]

Do you mean this for second option

Where you want to use Inner Join for first one

[vishsaggi]

Sorry Anil, I did not get you. I was looking for Grand Total in the bottom rather than in the right corner of the table. If you scroll towards right it show grand total for each row? Any ideas ?

[sunny_talwar · Accepted solution]

May be you need to use this:

= Sum(Aggr([BTU Value] * IF(Visit = 'XB' OR Visit = 'BR' OR Visit = 'TN', 1,0), Visit, ChDate, Enc))

[vishsaggi]

Thanks Sunny. I believe this should suffice my requirement. Will check once i am at work and get back. Appreciate you help.

[vishsaggi]

Spot on Sunny. That worked. I cant believe it did not strike me to do that way. Thanks very much for your help.

Just a small favor, I remember you did something to get two grand totals in pivot table in some previous posts, do you have that community link, just want to refer for my learning.

Thanks,

V.

[sunny_talwar]

I wish I was that good at keeping track of my old post responses. I always suffer looking for them and had to repeat it every time some ask the same question again. I need a blog for thing kind of stuff.