none
How to add up a PowerPivot Column

    Question

  • I have created a PowerPivot table with some measures calculated in DAX. With Grand Total turned on, only some of the measures will actually display a Grand Total. Looking at the DAX I can understand why the engine decides not to display, but nonetheless I still need to show a Grand Total. I don't know of a way to force the engine to do a simple SUM() on a column to display a Grand Total, thus I think my only option is to calculate the Grand Total outside the Pivot Table. The messy part is how to define the Column to the SUM() - since the Pivot Table size/shape is dynamic due to slicers. The best I can come up with at this point is to use MATCH("Grand Total"...) and MATCH("<col header>"...) along with OFFSET() to feed the range to the SUM(). This works, but sure looks messy. I am hoping someone has a better solution...
    Friday, August 30, 2013 3:22 PM

Answers

  • Normally, a simple total can be achieved with Measure = Sum(Column) nothing special. 

    I'm not sure why you're missing totals, have you filtered them out using FILTER() or CALCULATE()?


    _________________________________________________ Lee Hawthorn ACMA, CGMA View my Performance Management blog at leehbi.com

    Friday, August 30, 2013 3:42 PM

All replies

  • Normally, a simple total can be achieved with Measure = Sum(Column) nothing special. 

    I'm not sure why you're missing totals, have you filtered them out using FILTER() or CALCULATE()?


    _________________________________________________ Lee Hawthorn ACMA, CGMA View my Performance Management blog at leehbi.com

    Friday, August 30, 2013 3:42 PM
  • Looking at the DAX I can understand why the engine decides not to display, but nonetheless I still need to show a Grand Total.

    Hello,

    How does the DAX formula look like?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 30, 2013 3:45 PM
    Answerer