Calculate Sales Tracking against target

[Lodewyk]

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

[ahadkarimi]

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]

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

[ahadkarimi]

@LodewykI'll do it with pride.

[Lodewyk]

@ahadkarimi , please see above and the pbix

[ahadkarimi]

If you encounter any issues, let me know.

[Lodewyk]

Calculate-Sales-Tracking-against-target.pbix 

[ahadkarimi · Accepted solution]

Here is the PBIX file

[Lodewyk]

Thank you, much appreciate

[v-jialongy-msft]

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.