Forum Discussion

apkmnp's avatar
apkmnp
Frequent Visitor
4 years ago
Solved

Looking to restrict Total column calculation over specific months data only

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

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    apkmnp , Try like

     

    Measure =

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

    return

    divide([Actual] -_for,_For)

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    apkmnp , Try like

     

    Measure =

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

    return

    divide([Actual] -_for,_For)