Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
keremoyilmaz
Frequent 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 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

0 REPLIES 0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors