Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I'm doing a sourcing excercise to search the best 2 carriers per shipping point
The dataset is based on cost per shipment
-So first I need to sum all shipments per shipping point per carrier
-calculated which is the % of shipments they have quoted per shipping point
Then I need to create a scenario with the 2 best carriers per shipping point Taking into account only the carriers they have at least offered 70% of the shipments
Any ideas how I do that?
Below the data table
Shipment | carrier | Shipping point | Cost | kg |
1 | A | New york | 8 | 5 |
2 | A | LA | 21 | 10 |
3 | A | Miami | 18 | 5 |
4 | A | Boston | 19 | 10 |
5 | A | Boston | 19 | 80 |
6 | A | Boston | 5 | 500 |
7 | A | Boston | 25 | 11 |
8 | A | New york | 7 | 15 |
9 | A | LA | 22 | 7000 |
10 | A | Miami | 8 | 540 |
1 | B | New york | 14 | 5 |
2 | B | LA | 20 | 10 |
3 | B | Miami | 9 | 5 |
4 | B | Boston | 10 | |
5 | B | Boston | 80 | |
6 | B | Boston | 11 | 500 |
7 | B | Boston | 11 | 11 |
8 | B | New york | 22 | 15 |
9 | B | LA | 16 | 7000 |
10 | B | Miami | 7 | 540 |
1 | C | New york | 10 | 5 |
2 | C | LA | 5 | 10 |
3 | C | Miami | 24 | 5 |
4 | C | Boston | 20 | 10 |
5 | C | Boston | 80 | |
6 | C | Boston | 10 | 500 |
7 | C | Boston | 23 | 11 |
8 | C | New york | 13 | 15 |
9 | C | LA | 24 | 7000 |
10 | C | Miami | 9 | 540 |
1 | D | New york | 22 | 5 |
2 | D | LA | 23 | 10 |
3 | D | Miami | 22 | 5 |
4 | D | Boston | 10 | |
5 | D | Boston | 9 | 80 |
6 | D | Boston | 17 | 500 |
7 | D | Boston | 7 | 11 |
8 | D | New york | 10 | 15 |
9 | D | LA | 16 | 7000 |
10 | D | Miami | 17 | 540 |
The result I would like to see is
Shipping point | carrier | Rank | % Quoted | Total |
Boston | B | (blank) | 50% | 22 |
D | 1 | 75% | 33 | |
C | 2 | 75% | 53 | |
A | 3 | 100% | 68 | |
Boston Total | 176 | |||
LA | C | 1 | 100% | 29 |
B | 2 | 100% | 36 | |
D | 3 | 100% | 39 | |
A | 4 | 100% | 43 | |
LA Total | 147 | |||
Miami | B | 1 | 100% | 16 |
A | 2 | 100% | 26 | |
C | 3 | 100% | 33 | |
D | 4 | 100% | 39 | |
Miami Total | 114 | |||
New york | A | 1 | 100% | 15 |
C | 2 | 100% | 23 | |
D | 3 | 100% | 32 | |
B | 4 | 100% | 36 | |
New york Total | 106 | |||
Grand Total | 543 |
The step in bewteen I do in excel is following, but I would like to this now in BI
Shipping point | Shipments | Quoted shipments | % Quoted | Sum of Cost | carrier | Rank |
Boston | 4 | 4 | 100% | 68 | A | 3 |
LA | 2 | 2 | 100% | 43 | A | 4 |
Miami | 2 | 2 | 100% | 26 | A | 2 |
New york | 2 | 2 | 100% | 15 | A | 1 |
Boston | 4 | 2 | 50% | 22 | B | |
LA | 2 | 2 | 100% | 36 | B | 2 |
Miami | 2 | 2 | 100% | 16 | B | 1 |
New york | 2 | 2 | 100% | 36 | B | 4 |
Boston | 4 | 3 | 75% | 53 | C | 2 |
LA | 2 | 2 | 100% | 29 | C | 1 |
Miami | 2 | 2 | 100% | 33 | C | 3 |
New york | 2 | 2 | 100% | 23 | C | 2 |
Boston | 4 | 3 | 75% | 33 | D | 1 |
LA | 2 | 2 | 100% | 39 | D | 3 |
Miami | 2 | 2 | 100% | 39 | D | 4 |
New york | 2 | 2 | 100% | 32 | D | 3 |
@Anonymous,
I'm doing a sourcing excercise to search the best 2 carriers per shipping point
The dataset is based on cost per shipment
-So first I need to sum all shipments per shipping point per carrier
-calculated which is the % of shipments they have quoted per shipping point
Then I need to create a scenario with the 2 best carriers per shipping point Taking into account only the carriers they have at least offered 70% of the shipments
Could you share some calculate logic of your requirement?
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |