Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
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!
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])))
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |