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 ...
Sales vs Target
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