Matrix - variance measure showing incorrectly

[Anonymous]

Hi All, 

I've created two measures:

1. To calculate total budget:  Calculate(Sum([Amount]), Cost Type = "Budget')

2. To calculate YTD actuals and forecast for future months: 

YTD Actuals =

Var LastDate = CALCULATE(MAX([Data[MonthDate]), Data[Type] = "Actuals", REMOVEFILTERS())
RETURN 
IF(SELECTEDVALUE(DimDate[Date]) <= LastDate, 
CALCULATE(SUM(Data[Amount]), DATESYTD(DimDate[Date], "30/06"), Type="Actuals"))

Forecast for Future Months: 

Var YTD Forecast = TOTALYTD(SUM(Data[Amount]), Data[MonthDate], Data[Type] = "Forecast", "30/06")
RETURN 
CALCULATE([YTD Forecast], FILTER(Data, Data[Month Num] > SELECTEDVALUE(DimDate[FiscalMonthNum])))

Actuals + Forecast: 
YTD Actuals + Forecast for Future Months

I've pulled the Total Budget and Actuals+Forecast measures into a matrix visual that looks like this (see table below). It drills down from outsource and BAU costs into the cost types. I've pulled in my budget and variance measures into this visual and it works great. H

owever, when i do Budget - Forecast/Actuals to get the variance, it does a weird calculation and it gives me the incorrect variance. 

Would anyone know how to fix this? Thank you so much in adv!

Type	Cost Type	Amount
Outsource Costs	Budget	Measure 1
	Forecast	Measure 2
	Variance	Measure 1 - Measure 2
BAU Costs	Budget
	Forecast
	Variance

[MFelix · Accepted solution]

Hi @Anonymous ,

The use of measures is based on context and the calculation on overall totals are different from the ones on the single line, so the calculation sometimes can get weird because of the context transiction.

Check this article.

In this case and having you being a calculated measure based on an if statement believe that the best option is to have a SUMX type of calculation something similar to:

Variance = SUMX(VALUES(Table[Type]), [Budget] - [Forecast/Actuals])

Be aware that without detaisl about your model this may not work.

If you cannot achieve expected result can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Anonymous]

Thanks @MFelix !

I jsut added in SUMX function and "ALL" filter and it seemed to work now!