Variance from 100% while keeping row context

[RMDNA]

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?

[Phil_Seamark · Accepted solution]

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]

@Phil_Seamark,

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