Forum Discussion

keremoyilmaz's avatar
keremoyilmaz
Frequent Visitor
3 years ago

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 1Quarter 2Quarter 3Quarter 4
Target Quarter200200200200
Sales Quarter150160300140
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-AchievedAchieved = 100%
sales minus target (to be added to next quarter)0-4060

 

0

No RepliesBe the first to reply