DAX / PowerPivot - Handling Changing Sales Rep Targets When Promoted

0
votes

I am fairly new to DAX / PowerPivot but have built a nifty Sales Dashboard which is working well. Once combined with the PowerUpdate utility and Office365 PowerBI we will have a awesome solution. I'm excited!

One final thing that I am struggling to get my head around, is how we handle changing targets/budgets when a Sales Rep is promoted, as we assign budget by Job Level.

Let's say we have Sales Rep A - Junior Sales Rep with a target/budget of 30K per Quarter and they they get promoted in Q2 to Mid-Level with a target/budget of 45K per Quarter.

In my current dashboard, as soon as the Job Title is changed, this changes historical budget to the new target which warps any historic performance.

We have too many Sales Reps to create a quarterly budget table rows entry for each Sales Rep and each Quarter which seems to be how all the examples out there handle it - i.e:

  • Sales Rep A | 2015 | Q1 | 30000
  • Sales Rep A | 2015 | Q2 | 45000
  • Sales Rep A | 2015 | Q3 | 45000

My initial thoughts were to add a new user to the User Table with the same name and a "Budget Valid To" date and calculating target based off those dates.

  • Sales Rep A | Junior Sales Rep | 30000 | 01/01/2015
  • Sales Rep A | Mid - Sales Rep | 45000 | 01/04/2015

I just can't get my head around how I would handle this in a DAX calculation and I can't help thinking that there must be a more elegant way of handling this anyway! I'm so close to delivering this it tingles.

Let me know if you need an example or more detail.

Cheers, Ben

1
excelpowerpivotdaxpowerbi

1 Answers

0
votes

Not sure the granularity of your data (daily | weekly | monthly). But one idea that could work well is to divide the target by the granularity so you're effectively pro rating the target. Then you can use basic SUM functions at each quarter boundary to show accurate targets since the sum of the 'daily' targets would equal your hard coded targets. You'd need to be careful if your periods vary in number of days... dividing into months would make it uniform; but mid month promotions would still be hard. HTH.