cancel
Showing results for
Did you mean:
Frequent Visitor

## Monthly target based on actual

Goal:

Track sales team performance by their ability to hit their quotas (Forecast) for the year.  The chart will show what their quota (forecast) is for each month, actual sales for each month (revenue), and the average monthly target needed to achieve their end of year quotas (high level shows them at current pace this value is what they need to average for the ensuing months).

Problem:

I'm having difficulty with creating a measure for a dynamic Monthly Target.  This value should update each month based on actual sales (Revenue) achieved by that specific salesperson. The New Monthly Target Measure below does not work as I would like, and I'm currently stuck.

Measures created thus far:

Total Sales = SUM('Team Performance'[Revenue])
YTD Sales Target = (SUM('Team Performance'[Forecast])/12)*Month(TODAY())
YTD Target Diff = [Total Sales]-[YTD Sales Target]
New Montly Target = roundup([YTD Target Diff]/(12-MONTH(TODAY())),0)

Data:

1 ACCEPTED SOLUTION
Frequent Visitor

Maybe the issue is with my Measures that are calculating "totals".  It appears the total measure isn't looking at all the months but instead is only looking at each row.  Perhaps I need to use the CALCULATE function?

Desired results assuming I throw one salesperson into a table or matrix show below (images show the progress as the Today month changes).  As revenue is generated, the monthly target will adjust.

For Jan:

For Feb:

For March:

4 REPLIES 4
Super User III

Please provide sample data in usable format (not as a picture) .

Frequent Visitor

Thanks for the response.  Link above includes the .pbix & data file.

Resolver III

Can you share your expected output?

Frequent Visitor

Maybe the issue is with my Measures that are calculating "totals".  It appears the total measure isn't looking at all the months but instead is only looking at each row.  Perhaps I need to use the CALCULATE function?

Desired results assuming I throw one salesperson into a table or matrix show below (images show the progress as the Today month changes).  As revenue is generated, the monthly target will adjust.

For Jan:

For Feb:

For March:

Announcements