Forum Discussion
IF ELSE alternative solution
Hi,
I need help with the best way to approach this requirement. Technically I could write a long nested SWITCH and IF statements but it becomes clunky and difficult to maintain. I would really appreciate it if someone could point me to a more efficient approach.
I have a table with the list of number sold products in different categories. I need to run a report showing sales ratings (e.g. "High","Good","Average","Slow") for each category based on the number of products sold and the criteria for that category.
Table 1: Product Sold
Product | Category | Sold |
A | Category 1 | 10 |
B | Category 1 | 6 |
C | Category 1 | 5 |
D | Category 2 | 15 |
E | Category 2 | 20 |
F | Category 3 | 1 |
G | Category 3 | 2 |
H | Category 3 | 1 |
Sale Rating Criteria (this is the criteria, shown here in a table format but I don't have a table in PowerBI)
Category | High | Good | Average | Slow |
Category 1 | >15 | 10-15 | 9-5 | 4-0 |
Category 2 | >40 | 39-30 | 29-20 | 19-0 |
Category 3 | >5 | 5-4 | 3-2 | 1-0 |
The report needs to be like this
Category | Sale Count | Sale Rating |
Category 1 | 21 | High |
Category 2 | 35 | Good |
Category 3 | 4 | Good |
What I initially tried but didn't want to pursue further.
- Create a visual table that show Sale Count by Category.
- Create a New Calculation as below
Sale Rating = SWITCH(
True(),
[Category] == "Category 1", IF ([Sale Count] > 15), "High", IF ([Sale Count] >= 10), Good, .....
[Category] == "Category 2", IF ([Sale Count] > 40), "High", IF ([Sale Count] >= 30), Good, .....
[Category] == "Category 2", IF ([Sale Count] > 5), "High", IF ([Sale Count] >= 4), Good, .....
)
I would create something like a lookup table, and then write a measure to calculate the corresponding sales rating:
The table i created based on your sample data looks something like the above. I left the lower bounds empty for the High sales rating and just adjusted my DAX to handle that condition. Feel free to adjust as you need.
Sales Rating = CALCULATE(MAX('Criteria Table'[Attribute]), FILTER('Criteria Table', SUM('Products Sold'[Sold]) >= COALESCE('Criteria Table'[Lower], 999999) && SUM('Products Sold'[Sold]) <= 'Criteria Table'[Upper]))
2 Replies
I would create something like a lookup table, and then write a measure to calculate the corresponding sales rating:
The table i created based on your sample data looks something like the above. I left the lower bounds empty for the High sales rating and just adjusted my DAX to handle that condition. Feel free to adjust as you need.
Sales Rating = CALCULATE(MAX('Criteria Table'[Attribute]), FILTER('Criteria Table', SUM('Products Sold'[Sold]) >= COALESCE('Criteria Table'[Lower], 999999) && SUM('Products Sold'[Sold]) <= 'Criteria Table'[Upper]))Thank you Vicky for the quick response.
