### 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

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

Yielding…

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

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?

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] =

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.

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

Yielding…

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!

The End.

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

1. Kimmie08 says:

LOVE IT!!! Thank you, helped me tremendously today.

2. sorayarene says:

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

3. toto says:

could you explain more precisely what do you mean by “Then I wrote two “smart total” measures:” please ?