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'm working with some messy sales win/loss data, and I need help. My goal is to calculate vendor consideration rate and vendor win rate. The data table includes the following columns:
I've parsed the Losing Vendors field into separate rows in Power Query. The result for the 'Deals' table looks like this:
ID | Winning Vendor | Losing Vendors |
1 | Vendor A | Vendor B |
1 | Vendor A | Vendor C |
1 | Vendor A | Vendor D |
2 | Vendor B | Vendor A |
2 | Vendor B | Vendor D |
3 | Vendor B | Vendor C |
3 | Vendor B | Vendor E |
4 | Vendor C | Vendor A |
4 | Vendor C | Vendor B |
4 | Vendor C | Vendor D |
4 | Vendor C | Vendor E |
4 | Vendor C | Vendor F |
5 | Vendor D | |
6 | Vendor A | Vendor B |
6 | Vendor A | Vendor E |
6 | Vendor A | Vendor F |
7 | Vendor E | Vendor B |
7 | Vendor E | Vendor C |
7 | Vendor E | Vendor D |
7 | Vendor E | Vendor F |
8 | Vendor B | Vendor A |
8 | Vendor B | Vendor C |
8 | Vendor B | Vendor D |
8 | Vendor B | Vendor E |
8 | Vendor B | Vendor F |
Calculating the respective vendor win rates is easy enough:
Vendor Win Rate =
DIVIDE(DISTINCTCOUNT([ID]),
CALCULATE(DISTINCTCOUNT([ID]), ALL(Deals[Winning Vendor])),0)
Summarizing the data for Losing Vendors is just a straight COUNT of ID for each vendor in the Losing Vendors column.
My challenge is in joining the two calculations, since the vendor names are duplicated across the two columns. Put another way, how do I join the two different vendor name fields so I can get counts for both wins and losses for each vendor?
I tried creating a UNION table of distinct vendor names, but when I create a relationship with 'Deals'[Losing Vendors] and try to make it active, I get an error:
You can't create a direct active relationship ... because an active set of indirect relationships already exists between these tables.
The calculation I want to be able to make include:
Vendor Consideration Rate = ([Win Count] + [Loss Count]) / [Deal Count]
I then want to create a scatter plot of vendors with Vendor Consideration Rate on the x-axis and Vendor Win Rate on the y-axis.
I have created an Excel file with the result I want here:
And a .pbix file with what I've tried so far here:
Solved! Go to Solution.
@CloudHerder Probably the easiest way is to create a disconnected table that lists all of your vendors. We will call this the Vendors table and the column that holds the names of the vendors is called Vendor. Then you can put this into a visualization along with this measure:
Measure =
VAR __Vendor = MAX('Vendors'[Vendor])
VAR __Deals = COUNTROWS(DISTINCT('Deals'[ID]))
VAR __Wins = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Deals',[Winning Vendor] = __Vendor),"__ID",[ID])))
VAR __Losses = COUNTROWS(FILTER('Deals',[Losing Vendor] = __Vendor))
RETURN
DIVIDE(__Wins + __Losses, __Deals, 0)
@CloudHerder Probably the easiest way is to create a disconnected table that lists all of your vendors. We will call this the Vendors table and the column that holds the names of the vendors is called Vendor. Then you can put this into a visualization along with this measure:
Measure =
VAR __Vendor = MAX('Vendors'[Vendor])
VAR __Deals = COUNTROWS(DISTINCT('Deals'[ID]))
VAR __Wins = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Deals',[Winning Vendor] = __Vendor),"__ID",[ID])))
VAR __Losses = COUNTROWS(FILTER('Deals',[Losing Vendor] = __Vendor))
RETURN
DIVIDE(__Wins + __Losses, __Deals, 0)
@Greg_Deckler
Thank you. That works!
I had previously created a table of all vendors using a UNION statement, as follows:
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 |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |