Calculating Win Rate by Company

[RB_Barbour-ABI]

I am relatively new to Power BI and I am attempting to calculate the win rate between competing companies bidding on projects.

The data is set out to have a row per company relationship and so projects have multiple rows. It is possible for a project to have multiple winners and losers. Therefore each row shows the relationship between two companies.

For example, Project 1 has one winner (A) and two losers (B,C). Project 4 has 2 winners (A,C) and two losers (B,D).

The data is in the format as follows:

	Value	Win	Loss
Project 1	5	Company A	Company B
Project 1	5	Company A	Company C
Project 2	5	Company B	Company A
Project 2	5	Company B	Company C
Project 3	9	Company A	Company C
Project 4	6	Company A	Company B
Project 4	6	Company A	Company D
Project 4	6	Company C	Company B
Project 4	6	Company C	Company D
Project 5	8	Company C	Company A
Project 5	8	Company C	Company D

The ideal ouput would be be a matrix of a winning company against losing companies where the user can select companies by the use of a slicer:

Winning Company:	Company A	<-- Slicer
Losing Companies:		Win Number	Win Value	Loss Number	Loss Value	Total Bids	Total Bid Value	Win Rate Number (%)	Win Rate Value (%)
Company B		2	11	1	5	3	16	66.7	68.8
Company C		2	14	1	8	3	22	66.7	63.6
Company D		1	6	0	0	1	6	100.0	100.0

I have been able to replicate this in excel with COUTIFS and SUMIFS but have been unable to translate this into Power BI.

I apologise for the lack of DAX formulas but I am struggling to get anything close.

[v-yalanwu-msft · Accepted solution]

Hi, @RB_Barbour-ABI ;

Try it.

the above as win number and win value.

then create a meaure.

Loss Number = CALCULATE(COUNT([Loss]),FILTER(ALL('Table'),[Loss]=MAX('Table'[Win])&&[Win]=MAX('Table'[Loss])))+0

Loss value = CALCULATE(SUM([Value]),FILTER(ALL('Table'),[Loss]=MAX('Table'[Win])&&[Win]=MAX('Table'[Loss])))+0

Total Bids = COUNT([Loss])+[Loss Number]

Total Bid Value = SUM('Table'[Value])+[Loss value]

Win Rate Number (%) = COUNT([Loss])/[Total Bids]

Win Rate Value (%) = SUM('Table'[Value])/[Total Bid Value]

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RB_Barbour-ABI]

Hi @v-yalanwu-msft ,

Thank you very much, this solution is excellent.

Unfortunately, if I add a Total to the table to show the selected company's overall numbers, the loss measures return the last company number and not the total.

Is there any way around this?

[SpartaBI]

@RB_Barbour-ABI you can PM me if you want to do a quick zoom to look together