Forum Discussion
bid score
Hi All,
Need you assistance.
I have to prepare a dashboard in which I am trying to calculate the score of the bidders in an auction. The details are given below.
I have a list of 10 bidders:
| Bidders |
| Com-1 |
| Com-2 |
| Com-3 |
| Com-4 |
| Com-5 |
| Com-6 |
| Com-7 |
| Com-8 |
| Com-9 |
| Com-10 |
These bidders are partcipating in an auction in which 5 products are displayed. Additionally each bidder has to pay a signing bonus. For each bidder there is three types of bidding strategy - Aggresive/ Mild and Low. Each startegy has further sub levels.
A bidder has to choose a strategy against the types mentioned. The detailed table is shown below
| Strategy | Bonus | Prod-1 | Prod-2 | Prod-3 | Prod-4 | Prod-5 |
| Aggressive-1 | 47 | 191 | 15637 | 1774 | 32 | 2.01 |
| Aggressive-2 | 48 | 185 | 19319 | 2195 | 32 | 2.59 |
| Aggressive-3 | 50 | 199 | 14196 | 2082 | 30 | 2.89 |
| Aggressive-4 | 50 | 217 | 13582 | 2196 | 32 | 2.04 |
| Aggressive-5 | 50 | 168 | 18033 | 1787 | 32 | 2.69 |
| Mild-1 | 46 | 177 | 14272 | 1890 | 30 | 2.73 |
| Mild-2 | 45 | 161 | 17603 | 1610 | 32 | 2.76 |
| Mild-3 | 43 | 213 | 15624 | 1830 | 30 | 2.86 |
| Mild-4 | 45 | 154 | 17802 | 2010 | 30 | 2.97 |
| Mild-5 | 46 | 212 | 13525 | 1674 | 31 | 2.52 |
| Low-1 | 40 | 160 | 21488 | 1774 | 30 | 2.33 |
| Low-2 | 40 | 184 | 21952 | 2013 | 32 | 2.55 |
| Low-3 | 42 | 216 | 15789 | 1699 | 31 | 2.06 |
| Low-4 | 40 | 192 | 20664 | 2109 | 31 | 2.85 |
| Low-5 | 40 | 151 | 14715 | 1962 | 30 | 2.68 |
Based on whatever strategy they choose, a score is calculated. This score is a relative score. Each bidding parameter has a weights as given below
| Prod-1 | 20% |
| Prod-2 | 50% |
| Prod-3 | 10% |
| Prod-4 | 10% |
| Prod-5 | 10% |
If suppose there are three bidders Comp-1, Comp-2 and Comp-3
Comp-1 choses Aggresive 1 strategy
Comp-2 chooses Agressive 4 Strategy
and Comp-3 choses Mild-1 Strategy
So the score of Comp-1 would be (191/max(191,217,177)*0.2)+(15637/max(15637,13582,14272)*0.5+(1774/max(1774,2196,1890)*0.1)+(32/max(32,32,30)*0.1)+(2.01/max(2.01,2.04,2.73)*0.1)
Same for Comp2 and 3.
If I use hierachy slicer with bidder name and strategy under them, so as an when i keep selecting bidders the score should keep changing and it could be displayed.
I was able to do it on excel but got stuck on power BI and DAX.
Hope the information provided is sufficient,
Thanks All.
Regards
Raghu
Hi baronraghu,
>>If suppose there are three bidders Comp-1, Comp-2 and Comp-3Comp-1 choses Aggresive 1 strategy
Comp-2 chooses Agressive 4 Strategy
and Comp-3 choses Mild-1 Strategy
So the score of Comp-1 would be (191/max(191,217,177)*0.2)+(15637/max(15637,13582,14272)*0.5+(1774/max(1774,2196,1890)*0.1)+(32/max(32,32,30)*0.1)+(2.01/max(2.01,2.04,2.73)*0.1)
In your scenario, you help a bidder choose a strategy and arrive at a relative score as the example you posted above. So you need to all the possible strategies. You need to select some row from your table, which is hard in Power BI, becasue the column is the basic calculation item in Power BI using DAX.
Best Regards,
Angelia
4 Replies
Additionally if on the slicer only Comp1 is selected he would get the maximum score say 100
If Comp1 and Comp2 are selected then Comp 1 would get 100 and comp2 would get 98
Similarly if 3 are there then, Comp 1 with 100, comp2 with 98 and comp 3 with 95
Hi baronraghu,
>>Comp-1 choses Aggresive 1 strategyWhen do you show in Power BI, Comp-1 how to choose Aggresive 1 strategy? Is there any relationship? Please share more details.+
Thanks
AngeliaThe purpose of the model is to help a bidder choose a strategy and arrive at a relative score
I have an additional table which gives each bidder three option, Aggressive, Mild and Low like the one below.
Bidder Strategy Com-1 Aggressive Com-1 Mild Com-1 Low Com-2 Aggressive Com-2 Mild Com-2 Low Com-3 Aggressive Com-3 Mild Com-3 Low I have also updated a previous table,
Type Strategy Bonus Prod-1 Prod-2 Prod-3 Prod-4 Prod-5 Aggressive Aggressive-1 47 191 15637 1774 32 2.01 Aggressive Aggressive-2 48 185 19319 2195 32 2.59 Aggressive Aggressive-3 50 199 14196 2082 30 2.89 Aggressive Aggressive-4 50 217 13582 2196 32 2.04 Aggressive Aggressive-5 50 168 18033 1787 32 2.69 Mild Mild-1 46 177 14272 1890 30 2.73 Mild Mild-2 45 161 17603 1610 32 2.76 Mild Mild-3 43 213 15624 1830 30 2.86 Mild Mild-4 45 154 17802 2010 30 2.97 Mild Mild-5 46 212 13525 1674 31 2.52 Low Low-1 40 160 21488 1774 30 2.33 Low Low-2 40 184 21952 2013 32 2.55 Low Low-3 42 216 15789 1699 31 2.06 Low Low-4 40 192 20664 2109 31 2.85 Low Low-5 40 151 14715 1962 30 2.68 Hope this helps
Hi baronraghu,
>>If suppose there are three bidders Comp-1, Comp-2 and Comp-3Comp-1 choses Aggresive 1 strategy
Comp-2 chooses Agressive 4 Strategy
and Comp-3 choses Mild-1 Strategy
So the score of Comp-1 would be (191/max(191,217,177)*0.2)+(15637/max(15637,13582,14272)*0.5+(1774/max(1774,2196,1890)*0.1)+(32/max(32,32,30)*0.1)+(2.01/max(2.01,2.04,2.73)*0.1)
In your scenario, you help a bidder choose a strategy and arrive at a relative score as the example you posted above. So you need to all the possible strategies. You need to select some row from your table, which is hard in Power BI, becasue the column is the basic calculation item in Power BI using DAX.
Best Regards,
Angelia
