Switch Statement?

[Anonymous]

I have two formulas built:

A person is placed into different tiers depending upon the amount of active clients they manage. Their NPS level is decided based off of their NPS score. I also have this model:

Based off of this model, how can I set up a calculated formula that will be something along the lines of:

If [Tier] = "2" && [NPS Level] = "NPS Silver", Employee X is awarded $130

If [Tier] = "1" && [NPS Level] = "NPS Bronze", Employee X is awarded $100

etc.

etc.

So depending on whatever tier and nps level an employee has, the formula returns whatever dollar amount it amounts to in the model? I've built something once that did work the way I wanted it to, my issue was afterwards I could not use the measure mathematically. It was just a number that I couldn't add or sum. Thanks!

[Greg_Deckler]

Sample data would assist greatly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@Greg_Deckler Sure thing. It's pretty tricky to explain So I have data that looks like this, on a small scale:

 

Each employee has a number of clients they manage and what's called a NPS score. Based off of their client count, they get placed into one of these tiers:

Fred has 45 clients, so he goes into tier 2. Fred's NPS is 5.17, so he is in the NPS Bronze category.

Betty has 61 clients, so she goes into tier 1. Betty's NPS is 31.25 so she is in the NPS Gold category.

Marc has 25 clients so he goes into tier 3. Marc's NPS is -2.5 so he is not in any category (Below Target Goal).

So that is what I'm looking for my final result to be:

I have to do the exact same thing for 2 other employee stats as well (Retention and Satisfaction) that are both styled the same way. Figured if I can figure this out for one of the pieces, the others can be set up similar. The end result will ultimately be a grand total field of adding together an employee's NPS Bonus, Retention Bonus, and Satisfaction Bonus. Let me know if I need to further clarify anything...