Forum Discussion

Lodewyk's avatar
Lodewyk
Icon for Helper I rankHelper I
2 years ago
Solved

Calculate Sales Tracking against target

Hi

 

I have 2 tables namely the target table and sales table.

 

the target table indicates the resource, with the target and whether it is monthly or annually. When the target is annual, the SUM of sales must be checked against the yearly target. When the target period is set to monthly, then every month the resource must achieve that target, there are no carryovers from the previous month. 

 

I need to present how the employee is tracking against the annual and monthly target

 

 

 

 

Sales vs Target

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Lodewyk 

    Please try the following measure:

    Month target =
IF (
    CALCULATE (
        SELECTEDVALUE ( Sales[Sales] ) > SELECTEDVALUE ( Target[Target] ),
        FILTER ( 'Sales', SELECTEDVALUE ( Target[Period] ) = "Monthly" )
    ),
    "yes",
    "no"
)

     

    Year target =
IF (
    CALCULATE (
        SUM ( Sales[Sales] ) > SELECTEDVALUE ( Target[Target] ),
        FILTER ( ALL ( 'Sales' ), SELECTEDVALUE ( Target[Period] ) = "Annual" )
    ),
    "yes",
    "no"
)

     

     

    Result:

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ahadkarimi's avatar
    ahadkarimi
    Icon for Solution Specialist rankSolution Specialist
    2 years ago

    Hey Lodewyk, try this code, and if you encounter any issues, let me know.

    At first, go to "Modeling" tab and create a new calculated column in the Target table:

    MonthlyTarget = IF(Target[Period] = "Annual", Target[Target] / 12, Target[Target])

     Then, create a new measure in the Sales table to sum the sales year-to-date for employees with an annual target:

    CumulativeSales = 
CALCULATE(
    SUM(Sales[Sales]),
    FILTER(
        ALL(Sales),
        Sales[Resource] = MAX(Sales[Resource]) &&
        Sales[Reporting Month] <= MAX(Sales[Reporting Month])
    )
)

     After that, create a new measure to calculate how much of the target has been achieved:

    PercentageAchieved = 
IF(
    RELATED(Target[Period]) = "Annual",
    DIVIDE([CumulativeSales], RELATED(Target[Target]), 0),
    DIVIDE(SUM(Sales[Sales]), RELATED(Target[MonthlyTarget]), 0)
)

     then add your charts (table, bar, line) and a slicer

    • Lodewyk's avatar
      Lodewyk
      Icon for Helper I rankHelper I
      2 years ago

      Thanks for the above. After the Related syntax, no option appears. I tried to add the table and column name but it says does not exist. Please assist