Answered by:
Measure Grand total is wrong

Question
-
Hi,
I found it is wrong for grand total of measure / calculated field? Here is my sample and data:
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.
- Edited by Mike DietterickEditor Wednesday, December 3, 2014 9:20 PM typo
- Marked as answer by Wilson Wu Thursday, December 4, 2014 8:04 AM
Wednesday, December 3, 2014 9:18 PMAnswerer
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.
- Edited by Mike DietterickEditor Wednesday, December 3, 2014 9:20 PM typo
- Marked as answer by Wilson Wu Thursday, December 4, 2014 8:04 AM
Wednesday, December 3, 2014 9:18 PMAnswerer -
Hi Mike,
it works.
Thanks
Wilson
Thursday, December 4, 2014 8:04 AM