Hi All, I have a question that I can't crack with a simple solution but I am sure that there is one. I have some store data of sales/customer satisfaction and I want to rate it based on some cut offs that have been provided.
Users will use a slicer on the store/data period/KPI type to figure out what rating has been achieved in a card something along those lines.
Table 1: This has our store data
| Store | | Data Period | | Sales Value | | Satisfaction |
| Store A | | Last 1 Year | | $50,000 | | 0.98 |
| Store A | | Last 1 Year | | $42,500 | | 0.89 |
| Store A | | Last 1 Month | | $8,500 | | 0.93 |
| Store A | | Last 1 Month | | $7,225 | | 0.84 |
| Store B | | Last 1 Year | | $150,000 | | 0.76 |
| Store B | | Last 1 Year | | $127,500 | | 0.68 |
| Store B | | Last 1 Month | | $15,000 | | 0.71 |
| Store B | | Last 1 Month | | $12,750 | | 0.64 |
| Store C | | Last 1 Year | | $550,000 | | 0.57 |
| Store C | | Last 1 Year | | $467,500 | | 0.52 |
| Store C | | Last 1 Month | | $65,000 | | 0.52 |
| Store C | | Last 1 Month | | $55,250 | | 0.47 |
Table 2: The cut off table which determins if a store is given a high, med or low rating.
| Store | | Data Period | KPI Type | | High Symbol | | High Cut Off | | Med Symbol | | Med Cut Off | | Low Symbol | | Low Cut Off |
| Store A | | Last 1 Year | Sales | | > | | $100,000 | | >= | | $50,000 | | > | | $0 |
| Store A | | Last 1 Month | Sales | | > | | $10,000 | | >= | | $5,000 | | > | | $0 |
| Store B | | Last 1 Year | Sales | | > | | $200,000 | | >= | | $100,000 | | > | | $0 |
| Store B | | Last 1 Month | Sales | | > | | $20,000 | | >= | | $10,000 | | > | | $0 |
| Store C | | Last 1 Year | Sales | | > | | $500,000 | | >= | | $250,000 | | > | | $0 |
| Store C | | Last 1 Month | Sales | | > | | $60,000 | | >= | | $30,000 | | > | | $0 |
| Store A | | Last 1 Year | Customer Service | | > | | 0.90 | | >= | | 0.85 | | > | | - |
| Store A | | Last 1 Month | Customer Service | | > | | 0.85 | | >= | | 0.80 | | > | | - |
| Store B | | Last 1 Year | Customer Service | | > | | 0.90 | | >= | | 0.85 | | > | | - |
| Store B | | Last 1 Month | Customer Service | | > | | 0.85 | | >= | | 0.80 | | > | | - |
| Store C | | Last 1 Year | Customer Service | | > | | 0.90 | | >= | | 0.85 | | > | | - |
| Store C | | Last 1 Month | Customer Service | | > | | 0.85 | | >= | | 0.80 | | > | | - |
I want to sum the store sales and compare it the sales cut off and average the customer satisfaction and compare it to the cut off.
E.g. for Store A for the last 1 year, they have $92,500 in sales which means they are in the medium category (not more than 100,000 but more than $50,000). For customer satisfaction, they have an average rating of 0.935 for the last 1 year which is above the 0.90 required for a high rating. So sales would return 'Medium' while customer satisfaction would return 'High'.
The High/Med/Low symbol can one of 4 symbols, >, >=, <, <= (less than might relate to stock that was stolen/lost).
My thoughts made me think about just hardcoding the symbols but I wasn't even sure how to go about this. I feel like I need a nested if statement but I don't think this is a smart way to go about things as it can lead to an overly complicated statement. Does anyone have any help here?
My measure would have been similar to
SALES RATING =
VAR V_SALESRATING = SUM(TABLE1[SALES])
RETURN
IF(AND(
SELECTEDVALUE(TABLE2[HIGH SYMBOL] = '>=', V_SALESRATING >= SELECTEDVALUE(TABLE2[HIGH CUT OFF]), "HIGH RATING",
IF(AND(
SELECTEDVALUE(TABLE2[HIGH SYMBOL] = '>', V_SALESRATING > SELECTEDVALUE(TABLE2[HIGH CUT OFF]), "HIGH RATING",
IF(AND(
SELECTEDVALUE(TABLE2[HIGH SYMBOL] = '<=', V_SALESRATING < SELECTEDVALUE(TABLE2[HIGH CUT OFF]), "HIGH RATING",
IF(AND(
SELECTEDVALUE(TABLE2[HIGH SYMBOL] = '<', V_SALESRATING < SELECTEDVALUE(TABLE2[HIGH CUT OFF]), "HIGH RATING",
.... (many if statements later)
IF(AND(
SELECTEDVALUE(TABLE2[HIGH SYMBOL] = '>=', V_SALESRATING > SELECTEDVALUE(TABLE2[LOW CUT OFF]), "LOW RATING", " ")....)
Although I think that this method would work (I have not tested it), I don't think that it is a very smart way to go about this.
