cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate II
Advocate II

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

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 structureExample 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 configuredHow 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 totalsThe 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!

2 REPLIES 2
Super User IV
Super User IV

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])))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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.  

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors