locked
Measure Grand total is wrong RRS feed

  • Question

  • Hi, 

    I found it is wrong for grand total of measure / calculated field? Here is my sample and data: 

    Summary

    Here is the data:

    TotalForecast:=CALCULATE(SUM([Qty]), Table1[Data Type]="Forecast")

    TotalSales:=CALCULATE(SUM([Qty]), Table1[Data Type]="Sales")

    Diff:=[TotalSales]-[TotalForecast]

    ActualDemand:=if([Diff]<0,[TotalForecast],[TotalSales])

    Is there something wrong for my expression?

    Thanks

    Wilson

    Tuesday, December 2, 2014 6:09 AM

Answers

  • The "problem" is that measures actually calculate individually for each cell in a pivot.  What that means is that technically the grand total row in a pivot is not related to the rows above it.  Intuitively, it seems like it is related because often times the grand total is what you expect.  This can be a little confusing at first but there are some blog posts out there that do a good job of explaining the concept.

    Your current measure is looking at the Diff only as it pertains to the grand total row.  It sees 9 and therefore returns the value of TotalSales.

    The quickest fix is probably just to add one more measure that forces your current measure to evaluate itself in the proper context:

    ActualDemand2:=
    
    SUMX(
         VALUES(Table1[Product Type]), 
         [ActualDemand]
     )

    In PowerPivot functions that end in "x" are called iterators.  They let you change the granularity of a calculation by cycling thru the individual rows of a table you specify.

    In this case, the table I am using is VALUES(Table1[Product Type]), which is a single column table containing the unique values of the Product Type.

    Now your measure evaluates the sum for each Product Type (which ends up being the 4 results showing in your pivot table above for each Product) and adds those 4 results together for Grand Total.



    Wednesday, December 3, 2014 9:18 PM
    Answerer

All replies

  • The "problem" is that measures actually calculate individually for each cell in a pivot.  What that means is that technically the grand total row in a pivot is not related to the rows above it.  Intuitively, it seems like it is related because often times the grand total is what you expect.  This can be a little confusing at first but there are some blog posts out there that do a good job of explaining the concept.

    Your current measure is looking at the Diff only as it pertains to the grand total row.  It sees 9 and therefore returns the value of TotalSales.

    The quickest fix is probably just to add one more measure that forces your current measure to evaluate itself in the proper context:

    ActualDemand2:=
    
    SUMX(
         VALUES(Table1[Product Type]), 
         [ActualDemand]
     )

    In PowerPivot functions that end in "x" are called iterators.  They let you change the granularity of a calculation by cycling thru the individual rows of a table you specify.

    In this case, the table I am using is VALUES(Table1[Product Type]), which is a single column table containing the unique values of the Product Type.

    Now your measure evaluates the sum for each Product Type (which ends up being the 4 results showing in your pivot table above for each Product) and adds those 4 results together for Grand Total.



    Wednesday, December 3, 2014 9:18 PM
    Answerer
  • Hi Mike,

    it works.  

    Thanks

    Wilson

    Thursday, December 4, 2014 8:04 AM