Matrix table with Opportunities Won and Quota values, Quotas not showing if there are no wins

[Namkce]

I am having difficulty with a Sales model that I'm trying to develop in Power BI.  The ultimate goal is to have a Matrix table that will display our sales people's bookings and quotas so we can measure performance.  The data model employs the following tables/sources:

• Opportunity Records, Table from Dynamics 365
  • This is where our Sales people's bookings are kept
  • When opportunitytypename = "Won", it's considered a Booking
• User Records, Table from Dynamics 365
  • Contains information about our Salespeople, including EmployeeID and the GUID required to identify the Salespeople to the Opportunity Record (via ownerid)
• IndividualQuota Table, Excel file stored on Sharepoint
  • Contains a table that list each employee's Assigned Vertical, EmployeeID, and Quota for each month
    • Example of IndividualQuota File/table structure
  • During the transformation, I join the User record table to the Quota table via EmployeeID, to get the GUID required to create an inactive relationship from the IndividualQuota table to the Opportunity Records via IndividualQuota[GUID] = Opportunity[ownerid]
• DateTable, basic date dimension table called Timeframe
  • Has active relationship to QuotaTable's QuoteDate
  • Has active relationship to Opportunity's ActualCloseDate

The issue:

When I build the Matrix based on the Opportunity table, I am unable to get the Sale's persons' quota to show up properly.  Either, I get the grand total of Quotas for the current period regardless of who it's assigned to (including to people who do not have a quota), or if I use a measure, I get no quota if the sales person does not have any Bookings (Wins) during the timeperiod selected.

Below is an example of what I'm getting

How the Matrix is configuredThe Sum of QuotaValue shows same value each row, where as the QuotaValue (measure) doesn't show values if Salesperson has no Bookings that month, which distorts the totals

As you can see, my matrix has 4 value columns...  [Count of OpportunityID], a Measure that calculates [Wins YTD], [Sum of QuotaValue] (from IndividualQuota Table), and [QuotaValue (measure)].  The Counts of OppID and the Wins YTD are working correctly.  It's the Quotas that don't work.

Below is my measure formula:

QuotaValue (measure) =

VAR Quota = CALCULATE(SUM(IndividualQuotas[QuotaValue])

    ,USERELATIONSHIP(IndividualQuotas[SystemUserID],CRM_Opportunity[ownerid])

)

RETURN Quota

If I use USERELATIONSHIP(IndividualQuotas[SystemUserID],PBI_Users[SystemUserID]), I get 76 for each row like the sum of QuotaValue.  Is there a way for me to Lookup the Salesperson in the far left column to get the approprate Quota values for that person, and that it will also work when I expand the dateperiod to multiple months?

Please help, I'm unable to figure this out. Thank you in advance!

[amitchandak]

Try like

QuotaValue (measure) =CALCULATE(SUM(IndividualQuotas[QuotaValue])
    ,USERELATIONSHIP(IndividualQuotas[SystemUserID],CRM_Opportunity[ownerid])
)
//Or
QuotaValue (measure) =CALCULATE(SUM(IndividualQuotas[QuotaValue])
    ,filter(CRM_Opportunity, CRM_Opportunity[ownerid] = max(IndividualQuotas[SystemUserID])))

[Namkce]

@amitchandak, 

Thank you for your suggestions, unfortunately, they did not work. 

The first Measure formula you provided is the same as the one I've been using and does not display any quotavalue if the salesperson does not have any wins yet. 

The second measure formula suggested didn't return any values whatsoever.  If I create a measure =MAX(IndividualQuotas[SystemUserID]) and plop it in the matrix, I get the same GUID value for each row, so I don't think that method will work.