Forum Discussion

RMDNA's avatar
RMDNA
Solution Sage
8 years ago
Solved

Variance from 100% while keeping row context

Hi all,

 

I'm running into an issue trying to calculate a % variance from plan. Below is a picture of the raw % to plan and its formula, giving us correct values.

 

 

However, when we attempt to get variance (e.g. 7.3% instead of 107.3%) using the below formula, we lose row context and all locations are displayed, giving us a lot of -100%s.

 

 

Any workarounds?

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI RMDNA

     

    Does this get close?

     

    DTD PL % = 
VAR myVariance = DIVIDE( [Daily Sales] , [Daily Sales Plan] , Blank())
RETURN IF(NOT ISBLANK(myVariance) , 1 - myVariance)

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    HI RMDNA

     

    Does this get close?

     

    DTD PL % = 
VAR myVariance = DIVIDE( [Daily Sales] , [Daily Sales Plan] , Blank())
RETURN IF(NOT ISBLANK(myVariance) , 1 - myVariance)
    • RMDNA's avatar
      RMDNA
      Solution Sage
      8 years ago

      Phil_Seamark,

       

      Needed a small adjustment to handle +/- (changed "1 - myVariance" to "myVariance - 1") but otherwise it worked great. Thanks!