Sum a PowerPivot Column

PentaGalCXO

New Member
Joined
Jan 31, 2012
Messages
46
I'm near the finish line and am but a simple Grand Total away from declaring victory over a hairy PowerPivot Table with intense backend DAX. Problem is PowerPivot will not display a Grand Total on certain columns due to the nature of the DAX. I can live with showing a Grand Total in a cell outside the Pivot Table, but don't know a good way to calculate a sum of numbers appearing in a PivotTable column. To my knowledge, PowerPivot table columns are not addressable like normal Tables (i.e. sum(Table1[ColumnX])) - that would be too easy. I like to use GETPIVOTDATA in similar situations, but since the Grand Total is not displayed, GETPIVOTDATA will not work. And yes, the table itself is dynamic in size depending on slicers.

The best solution I can come up with is a complex series of Match & Offset functions that search for the "Grand Total" row and the desired column label, feeding that range into a Subtotal funtion. It works but it was hard to write, harder to understand, and too hard to support down the road. Any suggestions???

Here is what i have:

PPTable - Defined Name for the upper left corner of the Table
ColX - the column label for the column with no Grand Total
Grand Total is turned on for the PowerPivot columns, thus the label for the last row is "Grand Total"

Formula outside the table
=SUBTOTAL(9,OFFSET(PPTable,1, MATCH("ColX",OFFSET(PPTable,0,0,1,999))-1,MATCH("Grand Total",B:B,0)-ROW(PPTable)-1))
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,138
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top