Variable calculation

[nicole_csy]

Hello all,

I am having multiple variable with conditions and i will need to derived their difference accordingly. 

e.g. vPrevActaulYTD:
if($(vifYTDCalSUM), (if($(vIfSumAmount),SUM({<$(vPrevYrYTD),$(vFilterActual)>}value)/SUM([Exchange]),SUM({<$(vYTD),$(vFilterActual)>}value))), if($(vifYTDCalAVG), (if($(vIfSumAmount),SUM({<$(vPrevYrYTD),$(vFilterActual)>}value)/MonthNumber/SUM([Exchange]),SUM({<$(vYTD),$(vFilterActual)>}value)/MonthNumber)), (if($(vIfSumAmount),SUM({<$(vMTD),$(vFilterActual)>}value)/SUM([Exchange]),SUM({<$(vMTD),$(vFilterActual)>}value))) ))

vBudgetYTD:

if($(vifYTDCalSUM), (if($(vIfSumAmount),SUM({<$(vYTD),$(vFilterBudget)>}value)/SUM([Exchange]),SUM({<$(vYTD),$(vFilterBudget)>}value))), (if($(vIfSumAmount),SUM({<$(vYTD),$(vFilterBudget)>}value)/MonthNumber/SUM([Exchange]),SUM({<$(vYTD),$(vFilterBudget)>}value)/MonthNumber)), (if($(vIfSumAmount),SUM({<$(vMTD),$(vFilterBudget)>}value)/SUM([Exchange]),SUM({<$(vMTD),$(vFilterBudget)>}value))) ) )

when creating a table, i can reference the value of vBudgetYTD by using $(=vBudgetYTD) and reference the value of vPrevActaulYTD by using $(=vPrevActaulYTD) seperately.

Let's assume vBudgetYTD = $10,000 and vPrevActualYTD = $55,000

i will need to calculate the variance by vBudgetYTD - vPrevActualYTD, which should be  -45,000

However when i create a new measure with expression:
=$(=vBudgetYTD) - $(=vPrevYrActualYTD)
it will only show the value of $10,000 and not able to calculate

if i create a new variable vTest which combine the formular inside. then create a measure with $(=vTest), the value can be correctly record as -$45,000

vTest = 

//BudgetYTD

if($(vifYTDCalSUM), 

(if($(vIfSumAmount),SUM({<$(vYTD),$(vFilterBudget)>}value)/SUM([Exchange]),SUM({<$(vYTD),$(vFilterBudget)>}value))),

if($(vifYTDCalAVG),

(if($(vIfSumAmount),SUM({<$(vYTD),$(vFilterBudget)>}value)/MonthNumber/SUM([Exchange]),SUM({<$(vYTD),$(vFilterBudget)>}value)/MonthNumber)),

(if($(vIfSumAmount),SUM({<$(vMTD),$(vFilterBudget)>}value)/SUM([Exchange]),SUM({<$(vMTD),$(vFilterBudget)>}value)))

)

)

-

//PrevYrYTD

if($(vifYTDCalSUM), (if($(vIfSumAmount),SUM({<$(vPrevYrYTD),$(vFilterActual)>}value)/SUM([Exchange]),SUM({<$(vYTD),$(vFilterActual)>}value))),

if($(vifYTDCalAVG), (if($(vIfSumAmount),SUM({<$(vPrevYrYTD),$(vFilterActual)>}value)/MonthNumber/SUM([Exchange]),SUM({<$(vYTD),$(vFilterActual)>}value)/MonthNumber)),

(if($(vIfSumAmount),SUM({<$(vMTD),$(vFilterActual)>}value)/SUM([Exchange]),SUM({<$(vMTD),$(vFilterActual)>}value))) ))

What should i do to calculate the two variable difference? I will have the needs to display it in million, which means i hv to calculate  =  ($(=vBudgetYTD) - $(=vPrevYrActualYTD)) /1000000 but i'm not able to add a blanket () outside the two variables, it will populate error.

[Benoit_C]

Hello @nicole_csy,

Can you try without the = sign before the variable names ?

 Like this: =($(vBudgetYTD) - $(vPrevYrActualYTD)) / 1000000

Regards,
Benoit

[nicole_csy]

Hello @Benoit_C 

it is having Error in expression : ')' expected when i use the above expression

[Benoit_C · Accepted solution]

Hi @nicole_csy,

Maybe you could try a similar approach as you did with the vTest variable?

You could create a new variable that combines the formulas of vBudgetYTD and vPrevActualYTD and then use that variable in your measure.
 

For example, you can create a new variable vVar with the following formula:

vVar = $(vBudgetYTD) - $(vPrevActualYTD)

Then, in your measure, you can use the expression $(=vVar) to display the calculated difference between the two variables.
 

To display the result:

vVarInMillion= ($(vBudgetYTD) - $(vPrevActualYTD)) / 1000000

Then, in your measure, you can use the expression $(=vVarInMillion) to display the calculated difference in millions.

Is that working ?

Regards,

Benoit