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

Answer 1

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.

Answer 2

Hi Mike,

it works.  

Thanks

Wilson