Variance between values in same column based on specific category

[alycianw]

I think I need a DAX command but I am new and not sure the best way to accomplish my need.

I have 2 columns "Values" and "Version". I need to calcuate the variance between the Acutals and the Budget for each vendor.

Is this a measure I need to add or do I need to transform my data so its in different columns?

Below is the chart I am using and need to add a 'Variance' Column too and will need to use the Variance data for other graphs as well

This is some of the file I am using to show how the data is uploaded.

Thank you so much in advance for any assistance and advice.

[govindarajan_d · Accepted solution]

Hi @alycianw,

I see that Actuals and Budget are two different sets of rows in the table. I would suggest pivoting the table, but I see there is something called as Forecast as well. If you think you can have actuals, budget and forecast for every unique row by pivoting you can do that. 

If you don't want to pivot, you can try this DAX measure:

Variance = 
VAR __Actual = CALCULATE(Table[Value],Table[Version]="Actuals")
VAR __Budget = CALCULATE(Table[Value],Table[Version]="Budget")
VAR __Variance = __Actual - __Budget
RETURN __Variance

[govindarajan_d · Accepted solution]

Hi @alycianw,

Did the solution work?

If so, please accept as a solution!

[JamesFR06 · Accepted solution]

HI,

Try this

Measure Variance=

VAR __Actual = CALCULATE(Table[Value],Table[Version]="Actuals",allexcept(Table[Vendor])
VAR __Budget = CALCULATE(Table[Value],Table[Version]="Budget",allexcept(Table[Vendor])
VAR __Variance = __Actual - __Budget
RETURN __Variance

[govindarajan_d · Accepted solution]

Hi @alycianw,

I see that Actuals and Budget are two different sets of rows in the table. I would suggest pivoting the table, but I see there is something called as Forecast as well. If you think you can have actuals, budget and forecast for every unique row by pivoting you can do that. 

If you don't want to pivot, you can try this DAX measure:

Variance = 
VAR __Actual = CALCULATE(Table[Value],Table[Version]="Actuals")
VAR __Budget = CALCULATE(Table[Value],Table[Version]="Budget")
VAR __Variance = __Actual - __Budget
RETURN __Variance

[govindarajan_d · Accepted solution]

Hi @alycianw,

Did the solution work?

If so, please accept as a solution!

[alycianw]

Currently it is not working I am still trying to get it to work for me. If/when it does I will let you know and accept it.