Forum Discussion
Variance between values in same column based on specific category
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
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.
The accepted DAX solution is correct. For the use case you describe (Actuals vs Budget variance per vendor), the measure approach keeps the data model cleaner than transforming into separate columns.
One thing to know for the future: as you add more metrics, each one needs its own set of measures (Actual, Budget, Variance, Variance %). For teams where Finance keeps requesting new metrics, this compounds fast.
Flexa Tables on AppSource handles Actuals vs Budget comparison as a built-in column no DAX measures needed, works directly in the published report. Search "Flexa Tables" on AppSource if you want to explore an alternative approach.
