sum per catagory, rank and create sceanrio

[Anonymous]

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

[moumipanja]

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

[Anonymous]

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

[moumipanja]

@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.

[Anonymous]

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