Forum Discussion
keremoyilmaz
3 years agoFrequent Visitor
Help needed to create Sales over Target Measure
Dear Community,
I have a model with tables of sales, targets and custom date.
I need to calculate sales achievement with rules as explained below and with the example table which is showing all the possible scenarios.
- If sales / target ratio is less than 80%, "target is not achieved" No remaining for the next quarter.
- If sales / target ratio is between 80% and 100%, "target achieved with remaining"
- Remaining target is added to next quarter.
- In the next quarter, firstly remaining sales from previous quarter has to be closed and then calculation has to be made for that quarter
- If sales / target ratio is 100%, "target achieved"
- If sales / target ratio is above 100%, "target over-achieved", over achieved amount is added to next quarter for the calculation of ratio.
- All above works within quarter 1 to quarter 4 which means
- Quarter 1 resets everything, in other words no remaining or over achievement from quarter 4 previous year to quarter 1 this year.
Moreover, I will have a single select slicer with quarters and within a matrix visual, I need to show the target, sales and Achievement % columns.
I hope I could clearly explain the logic of this rule and what I need.
Thank you very much in advance for your time and for your help
| Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | |
| Target Quarter | 200 | 200 | 200 | 200 |
| Sales Quarter | 150 | 160 | 300 | 140 |
| Achievement % (after completion of remaining from previous quarter) | 75% | 80% | 130% | 100% |
| Achievement Status (min 80%) | Not achieved<80% | 80%<Achieved with remaining<100% | 100%<Over-Achieved | Achieved = 100% |
| sales minus target (to be added to next quarter) | 0 | -40 | 60 |
0 |
No RepliesBe the first to reply