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 ...
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
Community Champion
Community ChampionAnonymous 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])))
RETURN
SELECTCOLUMNS(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!
Thanks again!