topic Re: Calculate Sales Tracking against target in DAX Commands and Tips

Calculate Sales Tracking against target

Lodewyk — Sun, 11 Aug 2024 20:08:40 GMT

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

Re: Calculate Sales Tracking against target

Anonymous — Mon, 12 Aug 2024 01:59:14 GMT

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.

Re: Calculate Sales Tracking against target

ahadkarimi — Mon, 12 Aug 2024 02:16:51 GMT

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

Re: Calculate Sales Tracking against target

Lodewyk — Mon, 12 Aug 2024 05:13:34 GMT

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

Re: Calculate Sales Tracking against target

Lodewyk — Mon, 12 Aug 2024 05:22:18 GMT

ahadkarimi , please see above and the pbix

Re: Calculate Sales Tracking against target

Lodewyk — Mon, 12 Aug 2024 05:31:06 GMT

Calculate-Sales-Tracking-against-target.pbix

Re: Calculate Sales Tracking against target

ahadkarimi — Mon, 12 Aug 2024 14:31:26 GMT

LodewykI'll do it with pride.

Re: Calculate Sales Tracking against target

ahadkarimi — Mon, 12 Aug 2024 14:32:06 GMT

If you encounter any issues, let me know.

Re: Calculate Sales Tracking against target

ahadkarimi — Mon, 12 Aug 2024 14:33:45 GMT

Here is the PBIX file

Re: Calculate Sales Tracking against target

Lodewyk — Mon, 12 Aug 2024 18:09:33 GMT

Thank you, much appreciate