cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sbourgoyne
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:

Sbourgoyne_0-1611460199365.png

 

1 ACCEPTED SOLUTION

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:

Sbourgoyne_0-1611596389402.png

 

For Feb:

Sbourgoyne_1-1611596420926.png

 

For March:

Sbourgoyne_2-1611596461580.png

 

View solution in original post

4 REPLIES 4
lbendlin
Super User III
Super User III

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

https://drive.google.com/drive/folders/1TRYTTgoKDAVzHOr4g85dcEBLPxKZH9Ld?usp=sharing 

 

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

Can you share your expected output?

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:

Sbourgoyne_0-1611596389402.png

 

For Feb:

Sbourgoyne_1-1611596420926.png

 

For March:

Sbourgoyne_2-1611596461580.png

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors