I am having trouble with a DAX formula that is causing there to be a difference between the sum of my columns/rows and the grand totals, as seen in screenshot below. For example, the sum of the first row should be (3.74M) and the sum of the first column should be (3.57M).
I need a way for the grand totals and summing of this measure to take the sum of these rows rather than coming up with a separate calculation on the total rows. I understand conceptually that the measure looks at the context of the total row and calculates in that context, but I need a way around this.
My formula is complex, and so is my data model. I believe the issue is coming from the part of the equation called Var InflationRate, the Inflation Rate table consists of a certain rate, by plant, by month, by year, and by category. I've underlined the parts of the DAX below that I think is causing this issue, I also multiply different variables by the Var InflationRate in other parts of the DAX which I've also underlined. These columns all have relationships to other data tables in my report. I think that the total rows are calculating an average rate based on a plant in the row totals, and an average rate based on component in the column totals. How do I prevent this from happening, and rather take a sum of what is actually in the rows or columns instead of recalculating at the Grand Total?
DAX =
VAR IsFixed =
FILTER(
Build,
Build = "Fixed"
)
VAR IsVariable =
FILTER(
Build,
Build= "Var"
)
VAR FixedActualNI = CALCULATE([Gross Sales Act] + [Sales Act] + [Deductions Act] + [Ingredients Std] + [Packaging Std] + [Spend] +[Var] + [Reform],IsFixed)
VAR FixedActual_PER_1000_kgNI = CALCULATE(DIVIDE(FixedActualNI,([Total Act KG per Component] / 1000)), IsFixed)
VAR InflationRate =
IF(
HASONEVALUE('Inflation Rates'[Rate]),
MAX('Inflation Rates'[Rate]),
AVERAGE('Inflation Rates'[Rate]))
VAR FixedActualInflationNI = CALCULATE(([Gross Sales Act]
+ [Sales Act]
+ [Deductions Act]
+ [Ingredients Std]
+ [Packaging Std]
+ [Spend]
+ [Var]
+ [Reform]), 'Component Map - Enablers'[Inflation] = "Y",IsFixed)*InflationRate
VAR FixedActual_PER_1000_kgInflationNI = calculate(DIVIDE(FixedActualInflationNI,([Total Act KG per Component] / 1000)), IsFixed)
VAR FixedPY_Key = CALCULATE(MAX(Fiscal PD[Fiscal Year Key]),
ALLEXCEPT(Fiscal PD[Fiscal Year Code]))-1
VAR FixedPY = CALCULATE([Gross Sales Act] + [Sales Act] + [Deductions Act] + [Var] +
[Reform] + [Spend] + [Ingredients Std] + [Packaging Std],
REMOVEFILTERS(Fiscal PD[Fiscal Year Code]),
Fiscal PD[Fiscal Year Key]=FixedPY_Key,IsFixed)
VAR FixedPY_PER_1000_kg = calculate(DIVIDE(FixedPY , ([Total PY KG per Component]/1000)),IsFixed)
VAR FixedNetInflationvsPY = FixedActualNI - FixedActualInflationNI - FixedPY
VAR FixedNetInflationvsPYPerThousand = FixedActual_PER_1000_kgNI - FixedActual_PER_1000_kgInflationNI - FixedPY_PER_1000_kg
VAR VarActualNI = CALCULATE([Gross Sales Act] + [Sales Act] + [Deductions Act] + [Ingredients Std] + [Packaging Std] + [Spend] +[Var] + [Reform],IsVariable)
VAR VarActual_PER_1000_kgNI = CALCULATE(DIVIDE(VarActualNI,([Total Act KG per Component] / 1000)), IsVariable)
VAR VarActualInflationNI = CALCULATE(([Gross Sales Act]
+ [Sales Act]
+ [Deductions Act]
+ [Ingredients Std]
+ [Packaging Std]
+ [Spend]
+ [Var]
+ [Reform]), 'Component Map - Enablers'[Inflation] = "Y",IsVariable)*InflationRate
VAR VarActual_PER_1000_kgInflationNI = calculate(DIVIDE(VarActualInflationNI,([Total Act KG per Component] / 1000)), IsVariable)
VAR VarPY_Key = CALCULATE(MAX(Fiscal PD[Fiscal Year Key]),
ALLEXCEPT(Fiscal PD[Fiscal Year Code]))-1
VAR VarPY = CALCULATE([Gross Sales Act] + [Sales Act] + [Deductions Act] + [Var] +
[Reform] + [Spend] + [Ingredients Std] + [Packaging Std],
REMOVEFILTERS(Fiscal PD[Fiscal Year Code]),
Fiscal PD[Fiscal Year Key]=VarPY_Key,IsVariable)
VAR VarPY_PER_1000_kg = calculate(DIVIDE(VarPY , ([Total PY KG per Component]/1000)),IsVariable)
VAR VarNetInflationvsPY = (VarActual_PER_1000_kgNI - VarActual_PER_1000_kgInflationNI - VarPY_PER_1000_kg) * ([Total Act KG per Component]/1000)
RETURN
(CALCULATE(FixedNetInflationvsPY, IsFixed) + CALCULATE(VarNetInflationvsPY, IsVariable))
