Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a query with different offers (1 value column) per shipment, In de data I've also a shipping point
I want to create a scenario where I see the Best 2 carriers per shipping point. Whereafter I want to see a scenario what the split of the carriers would be per shipping point. Any ideas how I should start?
I was able to create a rankx, but after that I got stuck
As per your description of the problem, I assumed there are 2 parts of the probable solution. The cost requirement of being best is just my assumption and can be decided on your business requirements.
1. Best 2 carriers per shipping point offering lowest cost
2. Best 2 shipping points per carrier offering highest cost
Solution:
1. Best 2 carriers per shipping point offering lowest cost
Create the following 2 measures
Cost min per Shipping Point = MINX( KEEPFILTERS(VALUES('Sheet1'[Shipping Point])), CALCULATE(MIN('Sheet1'[Cost])) )
Rank min first = RANKX(ALL(Sheet1[Carrier]),[Cost min per Shipping Point],,ASC)
Set your visual level filter for Rank min first measure -> select 'is less than' and set the value as 3
2. Best 2 shipping points per carrier offering highest cost
Create the following 2 measures
Cost max per Carrier = MINX( KEEPFILTERS(VALUES('Sheet1'[Carrier])), CALCULATE(MAX('Sheet1'[Cost])) )
Rank max first = RANKX(ALL(Sheet1[Shipping Point]),[Cost max per Carrier],,DESC)
Set your visual level filter for Rank max first measure -> select 'is less than' and set the value as 3
Great,
Although I forgot 1 item
Not aal carriers offered every shipments per shipping point, I would like to add a extra filter on % that they have quoted like table below
any idea?
Shipment | carrier | Shipping point | Cost | kg |
1 | A | New york | 5 | 5 |
2 | A | LA | 17 | 10 |
3 | A | Miami | 16 | 5 |
4 | A | Boston | 12 | 10 |
5 | A | Boston | 10 | 80 |
6 | A | Boston | 10 | 500 |
7 | A | Boston | 22 | 11 |
8 | A | New york | 10 | 15 |
9 | A | LA | 12 | 7000 |
10 | A | Miami | 22 | 540 |
1 | B | New york | 24 | 5 |
2 | B | LA | 16 | 10 |
3 | B | Miami | 19 | 5 |
4 | B | Boston | 10 | |
5 | B | Boston | 80 | |
6 | B | Boston | 15 | 500 |
7 | B | Boston | 24 | 11 |
8 | B | New york | 18 | 15 |
9 | B | LA | 25 | 7000 |
10 | B | Miami | 10 | 540 |
1 | C | New york | 5 | 5 |
2 | C | LA | 18 | 10 |
3 | C | Miami | 14 | 5 |
4 | C | Boston | 17 | 10 |
5 | C | Boston | 80 | |
6 | C | Boston | 21 | 500 |
7 | C | Boston | 6 | 11 |
8 | C | New york | 8 | 15 |
9 | C | LA | 9 | 7000 |
10 | C | Miami | 12 | 540 |
1 | D | New york | 6 | 5 |
2 | D | LA | 24 | 10 |
3 | D | Miami | 14 | 5 |
4 | D | Boston | 10 | |
5 | D | Boston | 11 | 80 |
6 | D | Boston | 8 | 500 |
7 | D | Boston | 10 | 11 |
8 | D | New york | 21 | 15 |
9 | D | LA | 8 | 7000 |
10 | D | Miami | 24 | 540 |
@Anonymous Could you please elaborate a little more on what you would want to find from the table you posted above? I am afraid I didn't quite understand your question.
Hi,
Thx Sorry,
Some carriers didn't offered all the shipments per loading location, this because they are interested in that kind of shipments.
Therefore I would like to set first a filter on % that they made a offer for.
for example they neeed to at least offer 95% of all the shipments leaving that loading location before i would like to consider them for the best 1 or 2 in the ranking
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |