Calculation Help

[WorldWide1]

Greetings all --

Stuck on something that's probably pretty simple for most of you.

I have a data set that looks like this:

We have a series of Orders, several Provider Offers on each Order, and w/the corresponding Cost/Margin/Revenue on each Order #.  The logic in my workbook selects the Provider w/the lowest Total Revenue offer on each Order, resulting in a view like this:  

This view lists the chosen (by Lowest Total Revenue) Provider on each OrderID.  This is working correctly line by line.

However, when I try to Sum the Total Cost ($132,136 in the above view), it doesn't jive w/ the Total Cost column total ($133,426):  

I THINK what's happening is that my Total Cost calculation is returning the lowest Total Cost on each OrderID, not necessarily the Total Cost amount that is related to the Provider that has the lowest Total Revenue.

I'm currently calculating Total Cost/Sum by:  

Least Cost$/Shipment = MIN('SHIP HISTORY+RATES'[Total Cost/Shipment])  and then:  

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

Any help greatly appreciated!

Thank you.

[Ahmed-Elfeel · Accepted solution]

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 😅❤️

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[v-tejrama]

Hi  @WorldWide1 ,

Thank you  @Ahmed-Elfeel  for the response provided! 

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[Ahmed-Elfeel · Accepted solution]

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 😅❤️

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[WorldWide1]

Sorry to be hitting this one again but I'm just not understanding how this works in this situation.  The Total Cost for Totals calculation you provided was perfect!  

 SUMX (VALUES ('SHIP HISTORY+RATES'[ShipmentID]), VAR MinRevenueForOrder = MINX (FILTER(ALLSELECTED('SHIP HISTORY+RATES' ), 'SHIP HISTORY+RATES'[ShipmentID] = EARLIER ( 'SHIP HISTORY+RATES'[ShipmentID] ) ), 'SHIP HISTORY+RATES'[Total Revenue$/Shipment] ) VAR SelectedProviderCost = CALCULATE ( MIN ( 'SHIP HISTORY+RATES'[Total Cost/Shipment] ), FILTER ( ALL ( 'SHIP HISTORY+RATES' ), 'SHIP HISTORY+RATES'[ShipmentID] = EARLIER ( 'SHIP HISTORY+RATES'[ShipmentID] ) && 'SHIP HISTORY+RATES'[Total Revenue$/Shipment] = MinRevenueForOrder ) ) RETURN SelectedProviderCost )

But I'm not sure how to get the SCAC-specific Total Cost totals in this chart.  I keep getting this:  

The Total Cost on each SCAC (provider) should be:  

and then the difference b/t the Total Revenue and Total Cost on each line would be the Savings.

Thank you again for your help and anything else you can tell me about this is greatly appreciated!

Thank you.

Brian.

[Ahmed-Elfeel · Accepted solution]

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 :

SCAC/Rated	Total Revenue	Total Cost	Savings
RLCAC	"Revenue"	$98,149.14	Revenue - $98,149.14
FXNLC	"Revenue"	$33,204.67	Revenue - $33,204.67
CNWYC	"Revenue"	$17,414.69	Revenue - $17,414.69

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 ☺️❤️

[WorldWide1]

Thank you SO MUCH!  I really appreciate your time on this - perfect!

[WorldWide1]

This was perfect - thank you very much!

[danextian]

Hi @WorldWide1 

I’m not sure what’s happening here. The sample data you shared doesn’t line up with the results — your example only shows three orders, but the output has a lot more. To move forward, could you share a proper sample dataset (not just a screenshot), the expected output based on that same sample, and the reasoning behind it? If it’s easier, you can post a link to an Excel file or a sanitized PBIX stored in the cloud.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.