Calculation Help

Hi WorldWide1,

I wish i could understand your problem very well...So you Have:

• Multiple providers per OrderID, each with their own Total Cost, Total Margin, and Total Revenue

• You want to choose the provider with the lowest Total Revenue for each order

• Then for that chosen provider you want to sum their Total Cost across all orders

You said you are using:

• Least Cost$/Shipment = MIN('SHIP HISTORY+RATES'[Total Cost/Shipment])
• Least Cost$/Total = SUMX(VALUES('SHIP HISTORY+RATES'[ShipmentID]), [Least Cost$/Shipment])

This will find the minimum Total Cost for each ShipmentID across all providers  not the Total Cost of the provider with the lowest Total Revenue....So if the cheapest provider on revenue has a cost of $150 but another provider has a cost of $140 your measure picks $140 even though that provider wasn’t chosen by the lowest revenue rule....Am I right?

The Correct logic in your case if i was right....You need to:

• For each OrderID find the minimum Total Revenue

• That OrderID find the provider with that Total Revenue

• If there is a tie pick one (usually the first or the one with lowest cost too but your requirement says lowest revenue so ties may exist)

• Then take that providers Total Cost
• Sum across all orders

So you can also use DAX to solve this issuewith a calculated table for the chosen offers, or a measure using SUMX and MINX/MAXX logic.:

Total Cost For Lowest Revenue Provider =
SUMX (
    VALUES ( 'SHIP HISTORY+RATES'[OrderID] ),
    VAR MinRevenueForOrder =
        MINX (
            FILTER (
                ALL ( 'SHIP HISTORY+RATES' ),
                'SHIP HISTORY+RATES'[OrderID] = EARLIER ( 'SHIP HISTORY+RATES'[OrderID] )
            ),
            'SHIP HISTORY+RATES'[Total Revenue]
        )
    VAR SelectedProviderCost =
        CALCULATE (
            MIN ( 'SHIP HISTORY+RATES'[Total Cost] ),
            FILTER (
                ALL ( 'SHIP HISTORY+RATES' ),
                'SHIP HISTORY+RATES'[OrderID] = EARLIER ( 'SHIP HISTORY+RATES'[OrderID] )
                && 'SHIP HISTORY+RATES'[Total Revenue] = MinRevenueForOrder
            )
        )
    RETURN
        SelectedProviderCost
)

This measure ensures you pick the cost from the lowest revenue provider not the lowest cost overall

I hope i understood your issue and solved it 😅❤️

Hi WorldWide1,

Let me clarify the Problem first 

The Problem is hen you try to break down by carrier, the measure is still finding the lowest revenue provider for each shipment but then you're trying to see "what if we used THIS carrier instead" (which is a different logic)

In your Future Carrier Mix table, it looks like you want:

• For each carrier (RLCAC, FXNLC, CNWYC)

• Show the total cost if that carrier were chosen for all shipments where they are an option

You need a different measure that answers "What would be the total cost if we used THIS carrier for all shipments where they are available?" Right? If i am right so try this measure:

Carrier Specific Total Cost = 
VAR CurrentCarrier = SELECTEDVALUE('SHIP HISTORY+RATES'[SCAC/Rated])
RETURN
IF(
    NOT ISBLANK(CurrentCarrier),
    SUMX(
        VALUES('SHIP HISTORY+RATES'[ShipmentID]),
        VAR CurrentShipment = 'SHIP HISTORY+RATES'[ShipmentID]
        VAR CarrierCostForShipment =
            CALCULATE(
                MIN('SHIP HISTORY+RATES'[Total Cost/Shipment]),
                FILTER(
                    ALL('SHIP HISTORY+RATES'),
                    'SHIP HISTORY+RATES'[ShipmentID] = CurrentShipment &&
                    'SHIP HISTORY+RATES'[SCAC/Rated] = CurrentCarrier
                )
            )
        RETURN
            CarrierCostForShipment
    ),
    BLANK()
)

Also here is anothe bonus version if you want to see the cost for shipments where this carrier has the lowest revenue (rather than all shipments where they exist):

Carrier Cost When Lowest Revenue = 
VAR CurrentCarrier = SELECTEDVALUE('SHIP HISTORY+RATES'[SCAC/Rated])
RETURN
IF(
    NOT ISBLANK(CurrentCarrier),
    SUMX(
        VALUES('SHIP HISTORY+RATES'[ShipmentID]),
        VAR CurrentShipment = 'SHIP HISTORY+RATES'[ShipmentID]
        VAR MinRevenueForShipment =
            MINX(
                FILTER(
                    ALL('SHIP HISTORY+RATES'),
                    'SHIP HISTORY+RATES'[ShipmentID] = CurrentShipment
                ),
                'SHIP HISTORY+RATES'[Total Revenue$/Shipment]
            )
        VAR IsLowestRevenueCarrier =
            CALCULATE(
                COUNTROWS('SHIP HISTORY+RATES'),
                FILTER(
                    ALL('SHIP HISTORY+RATES'),
                    'SHIP HISTORY+RATES'[ShipmentID] = CurrentShipment &&
                    'SHIP HISTORY+RATES'[SCAC/Rated] = CurrentCarrier &&
                    'SHIP HISTORY+RATES'[Total Revenue$/Shipment] = MinRevenueForShipment
                )
            ) > 0
        RETURN
            IF(
                IsLowestRevenueCarrier,
                CALCULATE(
                    MIN('SHIP HISTORY+RATES'[Total Cost/Shipment]),
                    FILTER(
                        ALL('SHIP HISTORY+RATES'),
                        'SHIP HISTORY+RATES'[ShipmentID] = CurrentShipment &&
                        'SHIP HISTORY+RATES'[SCAC/Rated] = CurrentCarrier
                    )
                ),
                0
            )
    ),
    BLANK()
)

So to be focused you should:

• Use the first measure if you want "total cost if we used this carrier for all shipments where they are an option"

• Use the second measure if you want "total cost for shipments where this carrier actually has the lowest revenue"

And to Confirm your Understanding:

First Measure = "If we forced ALL shipments to use RLCAC (where RLCAC is an option) what would the total cost be?"

• This gives you: RLCAC = $98,149.14 | FXNLC = $33,204.67 | CNWYC = $17,414.69

• This matches the Second Screenshot 

Second Measure = "Show me the total cost for shipments where this carrier naturally has the lowest revenue (without forcing)"

• This would typically give you lower numbers since each carrier only wins some shipments

Oh i forgot the Savings column Sorry😅...For that you can use:

Carrier Specific Savings = [Carrier Specific Total Revenue] - [Carrier Specific Total Cost]

Where Carrier Specific Total Revenue uses the same pattern as your first measure but for revenue instead of cost.

So The Matrix Should look like this :

You could also add Savings % to see which carrier provides the best margin percentage

Savings % = [Carrier Specific Savings] / [Carrier Specific Total Revenue]

I hope i understood your problem very well..Let me Know if you have another questions ☺️❤️