Forum Discussion

MiloPowerBI's avatar
MiloPowerBI
Frequent Visitor
3 years ago
Solved

DAX IF Statement to compare % difference between two values and return TEXT

Hello all,    Hopefully a quick one for someone out there.   I have some data, like the image above where there is a list of materials, a Plan of what we planned to produce and Actual for wh...
Need Help
Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi MiloPowerBI ,

    According to your description, here's my solution.

    Create a calculated column in the Plan table.

    Perform to plan =
IF (
    ABS ( DIVIDE ( RELATED ( 'Actual'[Actual] ) - 'Plan'[Plan], 'Plan'[Plan] ) ) <= 0.1,
    "Hit",
    "Miss"
)

    Result:

     

    If you want a visual to show the number of the misses and hits, put the Perform to plan in a column chart both in X-axis and Y-axis, and select count in the Y-axis, get the result:

    I attach my sample below for your reference.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    Best regards,

    Community Support Team_yanjiang

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

Hi MiloPowerBI ,

According to your description, here's my solution.

Create a calculated column in the Plan table.

Perform to plan =
IF (
    ABS ( DIVIDE ( RELATED ( 'Actual'[Actual] ) - 'Plan'[Plan], 'Plan'[Plan] ) ) <= 0.1,
    "Hit",
    "Miss"
)

Result:

 

If you want a visual to show the number of the misses and hits, put the Perform to plan in a column chart both in X-axis and Y-axis, and select count in the Y-axis, get the result:

I attach my sample below for your reference.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Community Support Team_yanjiang

  • MiloPowerBI's avatar
    MiloPowerBI
    Frequent Visitor
    3 years ago

    Hello yanjiang, 

     

    That worked perfectly. Thank you.