Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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?
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 |
---|---|
104 | |
101 | |
81 | |
79 | |
67 |
User | Count |
---|---|
122 | |
110 | |
94 | |
82 | |
77 |