Convert data on a worksheet by selecting Insert > Table. Go to the Insert tab of the ribbon bar, then click the Table icon. Click back on the Customer Table, then click anywhere inside the data area. Getting Closer… Just Need to Get Both Totals to the Bottom. Before creating the PivotTable, let’s create a table from each of the sheets. Note the Absence of the Word “Total” From “Avg Grand.”Īdded those to the pivot instead of the original measures and, and added my new AvgGrandTotal dummy column to Rows (but NOT the GrandTotal dummy column in this case), yielding… Of course, I am *the* precise kind of person who gets giggly over this sort of stuff, so maybe I’m alone, heh heh.įirst I updated my dummy linked table to have a second column…Ī Second Dummy Column.
Intrigued? I hope so, because I absolutely love this one. Whoa! An Average-Based Grand Total AND a Sum-Based Grand Total in a Single Pivot? Behind the Scenes…
A very Excel-style trick, as David Hager would say. 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!Īnd yeah, a single calc column with a fixed value like =”Grand Total” does the trick – even in a normal pivot, yep.Ī great trick. Select the Totals & Filters tab and then check. To move a pivot table, click within the data of the pivot table, then click the Analyze tab under PivotTable Tools in the Ribbon, as pictured below. Voila! Not Impressed? OK, Try TWO DIFFERENT Grand Totals Then… By going to the Analyze ribbon and then clicking Options will open a pivot table options window. Now you just turn off Grand Totals on the Design ribbon… (Which Just Happens to Have Just One Value – the Text Value “Grand Total”) “Grand Total” Appears at Top AND Bottom, But the Top One is Our “Dummy” Field The trick is simple – add a dummy table to your Power Pivot data model:ĭummy Table Named “Totals” with a Single ColumnĪnd drag that dummy field to rows of the pivot, in top position… Very clumsy, and damn near impossible when you have two fields on rows like above. 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. 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. Grand Totals at the TOP of the Pivot? Yep, no problem. Let’s say you want your grand totals to appear at the top of your pivot, like this: I’ve been dying to write this post for awhile now.
0 kommentar(er)
