Looking to restrict Total column calculation over specific months data only

[apkmnp]

Hi Team,

I am trying to calculate simple %variance formula based on (Actuals- Fcst)/(Fcst) for my data.

At monthly level, this is working fine, however for some months we do not have actuals but the forecast data only but the Total Year level calculation is considering even the months where actuals data is not available by default and giving incorrect picture. 

Any ways to resolve this by exclusion of those months at Total level? More details attached in screenshot.

The total number should be (43,883,038 -  44,393,311)/(44,393,311) or -1.15% however this is coming as -46.2% right now

Thanks in advance for your inputs!!

Best Regards,

apkmnp

[amitchandak · Accepted solution]

@apkmnp , Try like

Measure =

var _for = sumx(filter(Values(Table[Month Year]), not(isblank([Actual])) ), [Target])

return

divide([Actual] -_for,_For)