Calculation Help

[WorldWide1]

Afternoon all --

Still new to PBI and have a project that is beyond me currently so any help would be appreciated.

Data I have contains Shipment numbers and origin and destination:

ShipmentID	Origin State	Origin Zip	Dest State	Dest Zip	Lane
221676109	CA	90040	PA	18073	CA-PA
222909285	PA	18073	CO	80125	PA-CO
223286717	PA	18073	CT	06824	PA-CT
223286766	PA	18073	MA	1240	PA-MA
223370917	PA	18073	RI	02919	PA-RI

and then for each of those shipments, I have a variety of Costs:

ShipmentID	SCAC	Rate Type	Cost	Cost Add-Ins Total	HC Total	Total Cost
221676109	ABFS	B	$         387.14	$                                       -	$                  -	$       387.14
221676109	ABFSC	C	$         606.50	$                                90.00	$                  -	$       696.50
221676109	CNWY	B	$         376.47	$                              125.00	$                  -	$       501.47
221676109	CNWYC	C	$         598.13	$                                55.00	$                  -	$       653.13
222909285	ABFS	B	$         948.94	$                                       -	$         200.00	$   1,148.94
222909285	ABFSC	C	$     1,090.29	$                                       -	$                  -	$   1,090.29
222909285	CNWY	B	$     1,398.21	$                                       -	$                  -	$   1,398.21
222909285	CNWYC	C	$         996.60	$                                       -	$           75.00	$   1,071.60
222909285	CTII	B	$     1,509.64	$                                       -	$           95.00	$   1,604.64
222909285	EXLA	B	$     1,490.09	$                                       -	$                  -	$   1,490.09
223286717	SAIAC	C	$         180.37	$                                       -	$                  -	$       180.37
223286717	UPGF	B	$         200.91	$                                       -	$                  -	$       200.91
223286717	UPGFC	C	$         234.72	$                                       -	$                  -	$       234.72
223286717	WARD	B	$         142.38	$                                60.00	$                  -	$       202.38
223286717	WARDC	C	$         177.47	$                                       -	$                  -	$       177.47
223286766	ABFS	B	$         325.83	$                                       -	$                  -	$       325.83
223286766	ABFSC	C	$         280.52	$                                       -	$                  -	$       280.52
223286766	CNWY	B	$         320.61	$                                       -	$                  -	$       320.61
223286766	CNWYC	C	$         249.80	$                                       -	$                  -	$       249.80
223286766	SAIAC	C	$         256.79	$                                       -	$                  -	$       256.79
223286766	UPGF	B	$         202.47	$                                       -	$                  -	$       202.47
223286766	UPGFC	C	$         234.72	$                                       -	$                  -	$       234.72
223370917	ABFS	B	$         231.18	$                                       -	$                  -	$       231.18
223370917	ABFSC	C	$         241.02	$                                       -	$                  -	$       241.02
223370917	CNWY	B	$         175.57	$                                       -	$                  -	$       175.57
223370917	CNWYC	C	$         206.60	$                                       -	$                  -	$       206.60

I need to do a few things w/this data:

1.  For each ShipmentID, find the lowest Total Cost and SCAC on each ShipID;

2.  Determine if any of the SCAC's w/Rate Type C are the lowest cost on any ID;

3.  Count the # of times a Rate Type C SCAC is lowest cost, by Lane;

4,  Make all of it subject to a filter where the user can include/exclude the pool of SCAC's;

5.  Analyze how each SCAC is doing (wins/lowest cost), by Lane, and have the visual show all Lanes, regardless of whether the SCAC we're analyzing has any wins.

In Tableau it looks like this:

So, all Lanes and shipments are included in the view, subject to filtering by SCAC, and then the "Shipment Award" is arrived at by analyzing a single SCAC, for example while all of the SCAC's are included, we're only analyzing CNWYC in the above visual.

I hope all of that makes sense and I do appreciate any help on this.

Thank you.

[rajendraongole1 · Accepted solution]

Hi @WorldWide1  - Create a calculated column to get the lowest cost for each

MinTotalCost =
CALCULATE(
MIN(Shipments[Total Cost]),
ALLEXCEPT(Shipments, Shipments[ShipmentID])
)

another calculated column for loweest cost:

LowestCost =
IF(Shipments[Total Cost] = Shipments[MinTotalCost], 1, 0)

Check if a Rate Type "C" SCAC is the Lowest Cost

LowestCostRateC =
IF(
Shipments[IsLowestCost] = 1 && Shipments[Rate Type] = "C",
1,
0
)

now create a measure to count the ratetype c

RateC_Wins =
CALCULATE(
COUNT(Shipments[ShipmentID]),
Shipments[LowestCostRateC] = 1
)

Now apply the filter condition from param table:

RateC_Wins =
CALCULATE(
COUNT(Shipments[ShipmentID]),
Shipments[LowestCostRateC] = 1
)

hope this helps

[WorldWide1]

Thank you!