Forum Discussion
Calculating Win Rate by Company
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.
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])))+0Loss value = CALCULATE(SUM([Value]),FILTER(ALL('Table'),[Loss]=MAX('Table'[Win])&&[Win]=MAX('Table'[Loss])))+0Total 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.
3 Replies
v-yalanwu-msftCommunity Support
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])))+0Loss value = CALCULATE(SUM([Value]),FILTER(ALL('Table'),[Loss]=MAX('Table'[Win])&&[Win]=MAX('Table'[Loss])))+0Total 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.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
Community Champion
RB_Barbour-ABI you can PM me if you want to do a quick zoom to look together
