Creating a Measure using different filtered values from the same column

[Anonymous]

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

[tamerj1]

@Anonymous 

You ca use

CALCULATE ( 

SUM ( [Amount] ),

[Scenario] = "Actual"

) - 

CALCULATE ( 

SUM ( [Amount] ),

[Scenario] = "Budget"

)

[Anonymous]

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.

/ CALCULATE(

SUM([Amount]),

[Scenario] = "Actual"

)

Thanks again

[tamerj1 · Accepted solution]

@Anonymous 

Please try

=
VAR Actual =
    CALCULATE ( SUM ( [Amount] ), [Scenario] = "Actual" )
VAR Budget =
    CALCULATE ( SUM ( [Amount] ), [Scenario] = "Budget" )
RETURN
    DIVIDE ( Actual - Budget, Actual )

[tamerj1]

Hi @Anonymous 

the variance between which Actual and which Budget? Meaning what are you slicing by? Date?

[Anonymous]

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