topic Variance between values in same column based on specific category in DAX Commands and Tips

Variance between values in same column based on specific category

alycianw — Thu, 29 Feb 2024 15:52:07 GMT

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.

Re: Variance between values in same column based on specific category

govindarajan_d — Thu, 29 Feb 2024 17:33:05 GMT

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

Re: Variance between values in same column based on specific category

govindarajan_d — Fri, 01 Mar 2024 02:11:31 GMT

Hi alycianw,

Did the solution work?

If so, please accept as a solution!

Re: Variance between values in same column based on specific category

alycianw — Mon, 04 Mar 2024 19:20:02 GMT

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.

Re: Variance between values in same column based on specific category

JamesFR06 — Mon, 04 Mar 2024 22:46:36 GMT

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

Re: Variance between values in same column based on specific category

PBIdashboards — Mon, 15 Jun 2026 03:24:26 GMT

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.

Re: Variance between values in same column based on specific category

carter_gray705 — Wed, 17 Jun 2026 09:17:21 GMT

You 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.).