IF Statement with tiers

[abeirne]

Hi I am trying to show the amount of bonuses my stores will receive based on a tier system. I have the tiers in a column that are matched to the store number. So the logic is that if certain criteria is met, as well as the store being in a certain tier, the managers get a certain amount of bonus. So all three criteria at the beginning (which are measures) must be met, and then the bonus will be determined by what tier the store is in. 

SM NS Bonus 95% =

IF(

[Labor Yes/No] = "YES" && [> $66.75 Combined] = "YES" && [NS 95%] = "YES", 

IF(

'HQsites'[Tier] = 1,

"200",

IF(

'HQsites'[Tier] = 2,

"400",

IF(

'HQsites'[Tier] = 3,

"600"

)

)

)

)

This is not returning anything and it will not work as a measure either (it is currently a column) I am not sure what to do and was hoping for some advice. I can explain more if needed. Thank you so much. 

[v-janeyg-msft]

So all three criteria at the beginning (which are measures) must be met

---

Hi, @abeirne 

Can you tell me what's means? [Labor Yes/No]  [> $66.75 Combined] [NS 95%]  are measure or column?

Can you share some sample data? If 'HQsites'[Tier]' is text, you can use "" like 'HQsites'[Tier] = "1". and if you need number result you don't use "200", change to 200.

Like this:

SM NS Bonus 95% =
IF(
[Labor Yes/No] = "YES" && [> $66.75 Combined] = "YES" && [NS 95%] = "YES",
Switch( 'HQsites'[Tier],
"1", 200,
"2", 400,
"3",600,
0),0)

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[amitchandak · Accepted solution]

@abeirne , Try like

SM NS Bonus 95% =
IF(
[Labor Yes/No] = "YES" && [> $66.75 Combined] = "YES" && [NS 95%] = "YES",
Switch( 'HQsites'[Tier],
1, "200",
2, "400",
3,"600" ,0)
,0)

if 0 is coming means all logic ar failing

[abeirne]

Yes unfortunately I am seeing all zeros. I had to remove the quotes from the bonus amounts, I am not sure why it isn't working. Maybe it is just a simple formatting issues with the data types?