cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lestinge_br
Frequent Visitor

Calculating pondered quota achievement for N months.

Hey guys,

 

I'm with a challenge here, and I'm not able to solve it with DAX so far. Thanks in advance for any help provided.

 

I have 2 main fact tables: Sales (with won date[date of commercial commitment], payment date[the date my customer has paid me], and the name of the salesperson) and Monthly Quota table (each salesperson quota for each month, that considers won date), all they have a date column like MMM-YYYY where I can relate the sum of sales won in a month with that month's quota.


I've as well two dim tables: Date and "Comission Rules".

 

The comission rule is the same for all salesperson, is based on quota achievement Ex.: having $1.000.000 of sales and $1.100.000 of quota, it means I have 110% or 1.1 of achievement, for a given salesperson on a given month. (it's a function, a business rule that is not proportional, ex.: 30% of quota achievement = 0% comission, 80% of quota = 80% of comission, and 110% of quota = 130% of comission). I have a table for this but I could also usa a "big IF", it's not that complex.

 

The output I need is the amount of comission that my backoffice need to pay for each salesperson that month. It's given by the sum of the comission from all sales that were paid in the earlier month for that salesperson. I don't care here about the won date, there are sales with a lot of different won dates being paid at the same month. But I need to know the quota achievement on each of that months.

 

Example:

- We are in October 2021 (M0), so I want to fetch all deals from Sales table that customers paid in Sep-2021 (M-1). I'll slice a date filter fot that.

- Among the sales paid in Sep-2021, there are many different won dates (Ex.: Sep, Aug, Jul, and maybe more (M-1, M-2,... M-N)).

- I need to assure for each of those months if the quota is achieved, and how much. This have to happen for "N" months, since I may have any number of months here. The quotas are different per month and different per salesperson, can't use averages here.

 

 

- The output I need is something like having a date slicer selecting "won date" (format MMM-YYYY), and returning something like:

 

= Divide((SUMX(sales), Filter (paid_date = SEP-2021)), (SUM(quota), FILTER (quota_date = SEP-2021))) + sum the same for one month earlier, and the month before, and so on until the end.  

 

 

From here I believe I can do the rest of the stuff, but I didn't manage to operate this so far.

I'm not sure if it may be done with DAX, and I'm ok using several measures or creating new columns / tables if needed.

Just let me know if I was no clear on something, and thanks everyone in advance.

 

Ty,

Daniel

1 REPLY 1
lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors