Select the best carrier with sum / rank / filter / count / count non blank

[Anonymous]

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

[v-yuta-msft]

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