skip to Main Content

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.

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 in Power Pivot Window (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…

Dummy Column Added to Rows

Yielding…

Grand Total at Top AND Bottom of Pivot

“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…

Turning off pivot grand totals

Turn Off the “Real” Grand Totals

And now you just have it at the top!

Grand totals ONLY at top of pivot!

Voila!

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?

In Power Pivot, We Can Have Two Grand Totals That Use Different Calcs - Like Sum and Avg!

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…

New dummy column in Power Pivot Data Model Window

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

Then I wrote two “smart total” measures:

[Smart Sold] =

IF(ISFILTERED(‘Totals'[AvgGrandTotal]),
   [Avg Sold],
   [Sold]  )

[Smart Stock] =

IF(ISFILTERED(‘Totals'[AvgGrandTotal]),
   [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.

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 - moves our subtotal to the bottom where it will LOOK LIKE a grand total!

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

Yielding…

Grand totals everywhere!  Some are SUM, some are AVERAGE.

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

image

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

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 7 Comments
  1. Hi,

    I was wondering if it’s possible only show grand totals and not the name of the row. For example, I made a P&L with Powerpivot and created subtotals for the gross margin. At the top you the name ‘gross margin’ and at the bottom you see ”gross margin grand total’. Is it possible to hide the top ‘gross margin’?

    Kind regards

  2. Another solution that worked for me was simply adding a column with a single value to one of the tables, which was ‘wrapping’ all values in the rows field. In my case I ‘wrapped’ all countries under ‘Global’ column (a column with text “Global” in all rows”). Then you just need to add the column name (again ‘Global’ in my case) to the ‘Rows’ field. Works as long as ‘Show all Subtotals at Top of a Group’ option is active.

  3. please help. have no idea how to show distribution for brand, because distribution for brand shouldn’t be calculated from distribution for the SKUs in this brand. please help.

Leave a Reply

Your email address will not be published. Required fields are marked *