Formula help

[Anonymous]

I am trying to write a formula based off a column named % EBITDA Discrepancy in my PowerBi (so the column is all a percent). I want the formula to include the following criteria:

• Anything that is less than -10% and greater than 10% gets a number/rating of 5
• Anything that is less than -5%(until it hits -10%) and greater than 5% (until it hits 10%) gets a number/rating of 3
• Anything that is less than 0% (until it hits -5%) and greater than 0% (until it hits 5%) gets a number/rating of 0

[az38]

Hi @Anonymous 

if your % EBITDA Discrepancy is a really column, not a measure try SWITCH()

Rating = 
SWITCH(TRUE(),
ABS([% EBITDA Discrepancy]) > 0.1, 5,
ABS([% EBITDA Discrepancy]) > 0.05, 3, 
0
)

[Anonymous]

Thank you!

How would I do it if it was a measure then?

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You can still use the formula above.

Measure 2 = SWITCH(TRUE(),ABS([Measure])>0.1,5,ABS([Measure])>0.05,3,0)

Best Regards,

Jay

Community Support Team _ Jay Wang

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