Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello all,
I am pretty new to Power BI and am not very experienced in creating measures using DAX commands so I apologize if my terminology is a bit off.
I am trying to create a measure that calculates the variance of Actual and Budget amounts from a table as seen below. I tried creating a quick measure to do the calculation, but it doesn't allow me to filter the "Amount" data by Actual or Budget so it just returns the same data. What DAX Commands can I use to filter the Amount by Scenario and create an equation to solve for variance
Thanks
Solved! Go to Solution.
@Anonymous
Please try
=
VAR Actual =
CALCULATE ( SUM ( [Amount] ), [Scenario] = "Actual" )
VAR Budget =
CALCULATE ( SUM ( [Amount] ), [Scenario] = "Budget" )
RETURN
DIVIDE ( Actual - Budget, Actual )
@Anonymous
You ca use
CALCULATE (
SUM ( [Amount] ),
[Scenario] = "Actual"
) -
CALCULATE (
SUM ( [Amount] ),
[Scenario] = "Budget"
)
Perfect this works thanks so much. What would I need to add to the code in order to get % variance? I tried adding the below code to the end but it said my syntax is wrong.
@Anonymous
Please try
=
VAR Actual =
CALCULATE ( SUM ( [Amount] ), [Scenario] = "Actual" )
VAR Budget =
CALCULATE ( SUM ( [Amount] ), [Scenario] = "Budget" )
RETURN
DIVIDE ( Actual - Budget, Actual )
Hi @Anonymous
the variance between which Actual and which Budget? Meaning what are you slicing by? Date?
Hey @tamerj1
The "Actual" and "Budget" come from the scenario column. For example, using the chart above, the "Actual" values would be 5, 7, 3, 4, and 7, and the "Budget" values would be 8, 18, 8 , and 6. I am slicing by month, but I didn't include that column in the above example, as I didn't think it was needed to figure out the equation.
To clarify, the equation would need to filter the Scenario column by either Actual or Budget and pull the amounts from the corresponding column. Please let me know if you need further clarifications via images or what not.
Thanks
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |