Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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
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
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
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
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.
User | Count |
---|---|
65 | |
46 | |
20 | |
18 | |
15 |
User | Count |
---|---|
120 | |
41 | |
40 | |
28 | |
23 |