Forum Discussion
Variance between values in same column based on specific category
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.
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 __VarianceHI,
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
6 Replies
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 __VarianceCurrently 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.
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 __VarianceYou don’t need to transform the data—just use a measure. Create separate measures for Actual and Budget using the **Version** column, then subtract: ```DAX Actuals = CALCULATE(SUM('Table'[Values]), 'Table'[Version] = "Actual") Budget = CALCULATE(SUM('Table'[Values]), 'Table'[Version] = "Budget") Variance = [Actuals] - [Budget] ``` Then use **Variance** in your visuals—it will stay dynamic with filters (vendor, etc.).
