# 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

• 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

### 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
• Hi Mike,

it works.

Thanks

Wilson

Thursday, December 4, 2014 8:04 AM