Dynamic Solution with Power Query to Move the Grand Total in Excel PivotTable

In the previous blog post, we saw how to move the grand total in excel PivotTable manually alternating source dataset. However, after that post, many of you requested a dynamic solution. This is possible using Power Query.

Let’s do it !!!

In the above dataset, we have Products, Location, Quantities Sold, and Transaction Count.

Let’s convert our dataset to Excel Table. Keep the cell on the dataset, and press the shortcut key CRTL + t

else you can Go To > Insert > Table

It is a good habit always to give your table a name here will call this “tbl_mydataset”

Next step we need to load the data into Power Query Editor

Go To > Data > From Table/Range

Once the data is loaded into Power Query Editor, create a reference of the query by selecting the query > right click > reference

After the query is referenced, GoTo > Query Settings Properties > Rename the Query, or the other way is to double click on the query itself to rename, or right click > rename

So many possible ways to do this…OMG!! 😱

Now we need to summarize the values related to products.

Select the column of the product and then click on Group By.

Sum the value of Qty Sold and Tcount

⚠️ In this scenario your New column name must be exactly the same as later we need to append them with our records.

The value we are seeing now is the summarized values, but the location column is missing and this is the most important trick here to make our Grand Total appear in our dataset.

As we know that the dataset which is appearing is the summarized values of the products of the location.

GoTo > Add Column > Customs Column > Column Name Location > Customs Column Formula will be a text value Grand Total

We can straight away append this query tbl_mydataset_sum with the tbl_mydataset and we will get records appended or, we can reference the query tbl_mydataset_sum and rename the referenced query to tbl_mydataset_result and then append the tbl_mydataset to tbl_mydataset_result

BAAM 💥💥💥

Go To Close & Load > Select > Only Creat connection

Select the Query tbl_mydataset_result > right click > Load To > PivotTable Report

Why do I prefer to load it as a PivotTable report, not as a table? I don’t like to create a new table for those records and then create a new pivot table, so in this way, we are straightway creating a Power Query connection to the PivotTable.

⚠️ Currently Mac doesn’t support loading it as a PivotTable report, for that you need to load the data on the worksheet and then create Pivot Report

It’s almost done now

Create the PivotTable Report & Remove the original grand total column which is on the extreme right side of the PivotTable.

⚠️ NOTE: Grand Total on the right side will always add the location Grand Total Rows values. Take care of it, remove the original PivotTable Grand Total before you share the file with someone to avoid confusion.

Go To > Design > Grand Totals > On for Column Only