Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Namkce
Advocate III
Advocate III

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
amitchandak
Super User
Super User

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

 

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.