Dynamically adding up pivot grand totals

[DSP Varma]

I am facing a big challenge to find a way to add dynamically the grand totals of different pivot tables in the same sheet. Can somebody help me out in this? I am uploading a sheet where we need to multiply grand total with the commission rate and add the commissions of different pivot tables in summary sheet. I converted Pivot table to simple excel table as it is more than 9 mb.

 

[r2c2]

@DSP Varma

Interesting and almost crazy problem to have. I think if you have actual pivots in a worksheet (your example file is just paste values of pivots), then you can use VBA to loop thru all of them like below.

Sub showGTs()
    Dim pt As PivotTable, ws As Worksheet
   
    Set ws = ActiveSheet
    On Error Resume Next
    For Each pt In ws.PivotTables
        Debug.Print pt.GetData(pt.GrandTotalName)
    Next pt
End Sub

As we don't have access to your workbook, I am not sure if the commission rate is a pivot report filter or value typed in. If it is part of report filters, you can pull that too, using below code.

Debug.Print pt.PivotFields("Commission Rate ").CurrentPage

Once you have both values, you can then calculate the total commission easily.

That said, if you have access to Power Query, then you can use it. In fact, I recommend using PQ to strip this data to extract Commission Rates and Grand Totals because it is just too much fun.

Using Power Query to extract commission rates, grand totals as a table:

1. Save your attachment as a XLS or CSV file.
2. Create a new file (or open one of your existing ones)
3. Go to PQ > From File > From Excel (or CSV)
4. Select entire spreadsheet
5. Click on "Edit" to Bring the raw pivot table data in to PQ
6. Filter Column 1 to show any rows with Commission Rate or Grand Total
7. This removes all clutter and leaves 2 rows per pivot (one with Commission Rate and another with Grand total row)
8. Name this query as "Data"
9. Expand the queries pane on left and right click on "Data" query and duplicate it.
10. Name the newly created query as "Commission Rates"
11. In commission rates query
    1. Filter column 1 so only commission rates remain
    2. Select first 2 columns, right click on column headers and choose "Remove other columns"
    3. Add column with index numbers starting from 1
    4. Rename this column as "Pivot number"
    5. Rename first 2 columns as Label and Rate
    6. We are done with this now. Go back to "Data" query.
12. In Data Query
    1. Filter away "Commission Rate" from column 1 so only Grand Totals remain.
    2. Add column with index numbers starting from 1.
    3. Rename this column as "Pivot num"
    4. Move the "Pivot num" column all the way to front
    5. Select Pivot num and Column 1 and right click on headers
    6. Choose "Unpivot other columns"
    7. Select first two columns now
    8. Go to transform > Group By
    9. Group by "Pivot num" and "Column 1" with aggregation filed as "Total" with Max of Value column
       
       
       
       
    10. Click ok and your grand totals from all pivots (where GT is not null) is ready.
13. Time for the merge
14. From Home ribbon, click on Merge queries
15. Merge Data query with Commission Rates query on Pivot num column
16. In the new column, just expand Rate alone
17. Select the newly created rate column
18. Go to Add Column > Standard > Multiply
19. Choose Total as multiplication column
20. Rename "inserted multiplication" column as "Commission"
21. Rename other columns as you see fit
22. Close and load only the data query to your workbook.

Done!

When you add more pivots to your frankenworkbook, just refresh the query in your new file. And all rates fetched.

Note: This assumes all pivots will have Grand Total field named as "Grand Total". If you change this, you need to adjust the steps in PQ. Also, each pivot will have a commission rate row just above it. It can be a report filter or manually typed in thing. Doesn't matter.

See attached workbook. Change query connection to the raw data file you uploaded in post #1.