Search code examples
daxpowerpivot

DAX grand total "wrong" for price variance

I have repeatedly ran into the following problem when calculating variances, that the grand totals are calculated wrong. Although they are not technically wrong, I want to calculate them differently. The picture below shows what I have constructed in PowerPivot.

data

Formulas:

ACT Sales EUR = CALCULATE([Sales EUR];FILTER(data;data[Type] = "ACT"))
ACT Sales/kg = DIVIDE([ACT Sales EUR];[ACT Sales KG])
FC Sales/kg = DIVIDE([FC Sales EUR];[FC Sales KG]
Quantity Variance = ([ACT Sales KG] - [FC Sales KG]) * [FC Sales/kg]
Price Variance = ([ACT Sales/kg] - [FC Sales/kg]) x [ACT Sales KG]

The total variance is equal to column [Sales ACT vs FC EUR], but I would like the grand total for the variances to be a simple sum of the rows, and not using the measure formula on the grand total. How should this be done correctly?

The column structure for the data is as follows (where Type is either ACT, BUD, FC):

| Date | Type | Product | EUR | KG |
Solution

  • To fix variance totals, you need to iterate over product and then sum up the results:

    Quantity Variance = (
SUMX(
   VALUES( Data[Product]),
   [ACT Sales KG] - [FC Sales KG]) * [FC Sales/kg]
)

    (same for the other variance)