How to compare Quota amount with Actuals for Sales reps

[ankitkalsara]

Hi team,

I am struggling to create view for Quota amount with Actual amount for Sales rep in Power BI. My source object is Opportunity from Salesforce and I have stored quota amount in excel file as below.

Attaching here the pbix file: pbix file 

Link to test data: test data 

Quota Month	Sales Team Member	Monthly Quota Target
Jan - 2021	A	1000
Feb - 2021	A	1000
Mar - 2021	A	1000
Jan - 2021	B	3000
Feb - 2021	B	3000
Mar - 2021	B	3000
Jan - 2021	C	2000
Feb - 2021	C	2000
Mar - 2021	C	2000

Now when I pull the records from Salesforce under Opportunity object, I get below data.

Close Date Month	Opportunity Close Date	Opportunity Owner	Opportunity Amount
Jan - 2021	10 Jan 2021	A	100
Jan - 2021	12 Jan 2021	A	200
Jan - 2021	15 Jan 2021	A	300
Jan - 2021	30 Jan 2021	C	400
Jan - 2021	31 Jan 2021	C	500
Feb - 2021	5 Feb 2021	B	600
Feb - 2021	10 Feb 2021	B	700
Feb - 2021	20 Feb 2021	A	800
Mar - 2021	7 Mar 2021	C	900

Based on the above tables, I am trying to generate table as below which shows how much amount each member generated per month against the quota amount per month.

Quota Month	Sales Team Member	Quota Amount	Opportunity Amount
Jan - 2021	A	1000	600
Jan - 2021	B	3000	0
Jan - 2021	C	2000	900
Feb - 2021	A	1000	800
Feb - 2021	B	3000	1300
Feb - 2021	C	2000	0
Mar - 2021	A	1000	0
Mar - 2021	B	3000	0
Mar - 2021	C	2000	900

[linh091]

I created unique keys in each table and model them with relationship

You can download PBI file here

[ankitkalsara]

Hi @linh091 : thank you for your reply but unfortunately I messed up in the sample data in pbix file.

Here is the updated pbix file.

If Close Date is 12-Jan-2021, 15-Jan-2021, 30-Jan-2021 then Close Date Month will be Jan 2021 and I incorrectly mentioned as Feb-2021.

I guess the unique key will not work in that case. Any help will be much appreciated. 

[linh091 · Accepted solution]

I still keep the original formula and relationship, then it gives exactly your expected result

Your new updated PBI file