, ,

Grand Total Mania! (Totals at Top & Multiple Totals)

Let’s start 2014 with a bang…

I’ve been dying to write this post for awhile now.  Let’s say you want your grand totals to appear at the top of your pivot, like this:

Grand Totals at the TOP of the Pivot?  Yep, no problem.

Typically you’d want to do this when your pivot is really “tall” – lots of rows – and you don’t want to force people to scroll down in order to see the grand total.

Pivots do NOT let you display grand totals at the top of the pivot – only the bottom – so a frequent workaround is to write a formula in Excel itself that sums the whole column of the sheet.  Very clumsy, and damn near impossible when you have two fields on rows like above.

How DO You Do It, Then?

The trick is simple – add a dummy table to your Power Pivot data model:

Dummy Table Named “Totals” with a Single Column
(Created via Linked Table)

And drag that dummy field to rows of the pivot, in top position…


“Grand Total” Appears at Top AND Bottom, But the Top One is Our “Dummy” Field
(Which Just Happens to Have Just One Value – the Text Value “Grand Total”)

Now you just turn off Grand Totals on the Design ribbon…

Turn Off the “Real” Grand Totals

And now you just have it at the top!


Not Impressed?  OK, Try TWO DIFFERENT Grand Totals Then…

Hey, that trick above is actually pretty old – in fact I only learned of it from someone else, who found it on the MrExcel forums, and the trick was intended to be used with normal (non Power Pivot) pivots!

And yeah, a single calc column with a fixed value like =”Grand Total” does the trick – even in a normal pivot, yep.

A great trick.  A very Excel-style trick, as David Hager would say.  But can a normal pivot do THIS?

Whoa!  An Average-Based Grand Total AND a Sum-Based Grand Total in a Single Pivot??

Behind the Scenes…

Intrigued?  I hope so, because I absolutely love this one.  Of course, I am *the* precise kind of person who gets giggly over this sort of stuff, so maybe I’m alone, heh heh.

First I updated my dummy linked table to have a second column…

A Second Dummy Column.  Note the Absence of the Word “Total” From “Avg Grand.”

Then I wrote two “smart total” measures:

[Smart Sold] =

   [Avg Sold],

[Smart Stock] =

   [Avg Stock],
   [Total Stock]

Added those to the pivot instead of the original measures [Sold] and [Total Stock], and added my new AvgGrandTotal dummy column to Rows (but NOT the GrandTotal dummy column in this case), yielding…

Getting Closer…  Just Need to Get Both Totals to the Bottom.

Next, on the Options ribbon tab, I go to field settings for my Avg Grand field…

Uncheck “Display Subtotals at the top of each group” checkbox for our dummy field


Then we hide the row of the worksheet that contains that “empty” subtotal from our dummy column…


Right Click Row Header, Choose Hide

And we’re done!

Two Grand Total Rows in A Single Pivot - One is Average, One is Sum. Power Pivot for the Win.

The End.

Download the Workbook!

I’ve made this workbook available for download here so you can conduct your own experiments Smile

Read more on our blog

Get in touch with a P3 team member

  • Hidden
  • Hidden
  • This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

Related Content

Exciting Improvements to Power BI’s Export to Excel

It is a standing joke in the analytics industry that “Export to

Read the Blog

The case of the disappearing Field Parameters: SOLVED

The Case: The new Field Parameters feature from Microsoft had been added

Read the Blog

Completing the Set Up: Field Parameters Using Tabular Editor

May 2022’s release of Power BI Desktop is the best releases we’ve

Read the Blog

Calculation Groups to the Rescue!

o set the stage, I need you to travel back in time

Read the Blog