Hi all, I think I have a particularly tricky one to ask today. I am trying to obtain the number of sales from multiple stores and compare it to the targets that they have in place for each store and each month. To do this I am trying to retreive the less/greater than symbol from a table where the targets live and compare to the monthly sales from the stores. I want to try and do this in a measure.
The target table looks like the below. You can see the operator and target helps tell you how we rate the store's performance.
E.g. If the Alabama store sells more than $45,000 then it is given green, if above $40,000 (but less than $45,000) then orange and less than $40,000 is red.
| Month | | Metric | | Store | | Cut Off | | Operator | | Target Amount |
| 01-Jan-24 | | Target Sales | | Store Alabama | | Green | | > | | $45,000 |
| 01-Jan-24 | | Target Sales | | Store Alabama | | Orange | | > | | $40,000 |
| 01-Jan-24 | | Target Sales | | Store Alabama | | Red | | <= | | $40,000 |
| 01-Feb-24 | | Target Sales | | Store Alabama | | Green | | > | | $50,000 |
| 01-Feb-24 | | Target Sales | | Store Alabama | | Orange | | > | | $40,000 |
| 01-Feb-24 | | Target Sales | | Store Alabama | | Red | | <= | | $40,000 |
| 01-Jan-24 | | Target Sales | | Store Boston | | Green | | > | | $30,000 |
| 01-Jan-24 | | Target Sales | | Store Boston | | Orange | | > | | $25,000 |
| 01-Jan-24 | | Target Sales | | Store Boston | | Red | | <= | | $25,000 |
| 01-Feb-24 | | Target Sales | | Store Boston | | Green | | > | | $100,000 |
| 01-Feb-24 | | Target Sales | | Store Boston | | Orange | | > | | $95,000 |
| 01-Feb-24 | | Target Sales | | Store Boston | | Red | | <= | | $95,000 |
The sales table is simple
| Month | | Metric | | Store | | Sales |
| 01-Jan-24 | | Sales | | Store Alabama | $ 30,000 |
| 01-Feb-24 | | Sales | | Store Alabama | $ 80,000 |
| 01-Jan-24 | | Sales | | Store Boston | $ 27,500 |
| 01-Feb-24 | | Sales | | Store Boston | $ 120,000 |
At the end of this, I want to be able to create a measure where I can compare the sales figure to the targets and give a rating. If it is green then I will give it a 2, orange a 1 and red a 0. I can use this as conditional formatting. I'm just not sure how to go about this, is it like an if statement?
Any assistance is appreciated
