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