Forum Discussion
Creating a Measure using different filtered values from the same column
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
Anonymous
Please try
= VAR Actual = CALCULATE ( SUM ( [Amount] ), [Scenario] = "Actual" ) VAR Budget = CALCULATE ( SUM ( [Amount] ), [Scenario] = "Budget" ) RETURN DIVIDE ( Actual - Budget, Actual )
5 Replies
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
Anonymous
You ca use
CALCULATE (
SUM ( [Amount] ),
[Scenario] = "Actual"
) -
CALCULATE (
SUM ( [Amount] ),
[Scenario] = "Budget"
)
Anonymous
Please try
= VAR Actual = CALCULATE ( SUM ( [Amount] ), [Scenario] = "Actual" ) VAR Budget = CALCULATE ( SUM ( [Amount] ), [Scenario] = "Budget" ) RETURN DIVIDE ( Actual - Budget, Actual )