topic Re: Creating a Measure using different filtered values from the same column in DAX Commands and Tips

Creating a Measure using different filtered values from the same column

Anonymous — Wed, 29 Jun 2022 18:47:03 GMT

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

Re: Creating a Measure using different filtered values from the same column

tamerj1 — Wed, 29 Jun 2022 18:54:57 GMT

Hi Anonymous

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

Re: Creating a Measure using different filtered values from the same column

Anonymous — Wed, 29 Jun 2022 20:45:01 GMT

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

Re: Creating a Measure using different filtered values from the same column

tamerj1 — Wed, 29 Jun 2022 21:29:56 GMT

Anonymous

You ca use

CALCULATE (

SUM ( [Amount] ),

[Scenario] = "Actual"

) -

CALCULATE (

SUM ( [Amount] ),

[Scenario] = "Budget"

)

Re: Creating a Measure using different filtered values from the same column

Anonymous — Fri, 01 Jul 2022 18:30:54 GMT

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

Re: Creating a Measure using different filtered values from the same column

tamerj1 — Fri, 01 Jul 2022 18:37:13 GMT

Anonymous

Please try

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