Hello all,
Wondering if someone may be able to assist. I need to somehow create a measure that can provide me with the desired % of Target readings based on some rules as set out in the example below.
Raw data looks something like this - where a row represents an individual sales record in the given month (Note: I have created a Calculated Column called Percentage of Target already that gives the column on the right):
| State | Salesperson | Quota | Closed | Percentage of Target |
| QLD | Joe Bloggs | $ 1.00 | $ 799.00 | 79900% |
| QLD | Joe Bloggs | $ 1.00 | $ 799.00 | 79900% |
| QLD | Joe Bloggs | $ 1.00 | $ 399.00 | 39900% |
| NSW | Jane Doe | $ 2,500.00 | $ 1,400.00 | 56% |
| NSW | Jane Doe | $ 2,500.00 | $ 2,000.00 | 80% |
| WA | Sarah Lee | $ 5,000.00 | $ 8,000.00 | 160% |
| VIC | Jeff Smith | $ 10,000.00 | $ 9,000.00 | 90% |
| VIC | Jeff Smith | $ 10,000.00 | $ 6,000.00 | 60% |
| VIC | Jeff Smith | $ 10,000.00 | $ 3,000.00 | 30% |
Below is a view of what a SUM of the "Percentage of Target" calculated column provides versus what I really need to appear:
| | Current Percentage of Target | Desired % of Target |
| Joe Bloggs | 199700% | 100% |
| Jane Doe | 136% | 100% |
| Jeff Smith | 180% | 180% |
| Sarah Lee | 160% | 100% |
The quotas and the rules are as follows:
| | Normal Quota | | |
| Joe Blogs | $ 10,000.00 | In his first Quarter ($1 Quota) |
| Jane Doe | $ 10,000.00 | In her 4th Month (25% of Normal Quota) |
| Sarah Lee | $ 10,000.00 | In her 5th or 6th Month (50% of Normal Quota) |
| Jeff Smith | $ 10,000.00 | After 6th month (Normal Quota) |
Main rule: If a sales person is in a ramp up period (i.e. prior to the 7th month) if they exceed 100% of their target - it should appear as 100% instead of the actual percentage achieved against the reduced ramp up target.
Any ideas on how best to achieve this result that I could display in a dashboard?
