Forum Discussion
Calculate using columns with duplicated data
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:
- ID: a unique ID for each deal
- Winning Vendor: the name of the vendor who won the deal
- Losing Vendors: the names of all other vendors considered, but not chosen (delimited with semicolons)
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:
Anonymous 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)
2 Replies
Anonymous 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:All Vendors =VAR vendorTable = FILTER( DISTINCT( UNION(VALUES(Deals[Winning Vendor]),VALUES(Deals[Losing Vendors]))),NOT( ISBLANK([Winning Vendor])))RETURNSELECTCOLUMNS(vendorTable, "Vendor", [Winning Vendor])My problem was that I had linked it back to the original 'Deals' table. When I disconnect it and use it in the measure you provided, it also works.
Thanks again!