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?
User | Count |
---|---|
94 | |
86 | |
78 | |
68 | |
63 |
User | Count |
---|---|
113 | |
99 | |
97 | |
64 | |
59 |