Calculate Truck costs from a list of orders using a lookup table

[DaGemsta]

Afternoon all!

I'm struggling to get this to work and wondered if anyone can help please?

I have two tables.

ORDERS

FLEETMASTERLIST

ORDERS[Truck] has a Many:1 relationship with FLEETMASTERLIST[Fleet Name.2]

The list of ORDERS has the Document Number, Delivery Date, Location (Depot) and Truck for each order that goes out.

My FLEETMASTERLIST table shows me all the Truck information for our 56 vehicles. This includes their "Truck Daily Cost" which is a wholenumber entered manually by the Transport manager.

The Truck Daily Cost needs to be counted ONCE per day for each Truck that show on orders for that day. So a truck may show on 6 orders on the 1st of Nov, but the Cost for that Truck is only counted once. If the truck is on 6 orders for the 1st Nov and 3 orders on the 2nd Nov then the Truck Cost would be counted Twice (once per day)

Here's some sample date

ORDERS

Document Number	Location	Truck	Delivery Date
SO0185615	Grays	EU66 SYO	01/11/2021 00:00
SO0185656	Grays	EU66 SYO	01/11/2021 00:00
SO0185845	Grays	EU66 SYO	01/11/2021 00:00
SO0187040	Grays	EU66 SYO	01/11/2021 00:00
SO0187081	Grays	EU66 SYO	01/11/2021 00:00
SO0187400	Grays	EU66 SYO	01/11/2021 00:00
SO0183498	Grays	EU67 ZPZ	01/11/2021 00:00
SO0185359	Grays	EU67 ZPZ	01/11/2021 00:00
SO0187002	Grays	EU67 ZPZ	01/11/2021 00:00
SO0187054	Grays	EU67 ZPZ	01/11/2021 00:00
SO0181495	Grays	EJ19 VYG	01/11/2021 00:00
SO0181497	Grays	EJ19 VYG	01/11/2021 00:00
SO0182954	Grays	EJ19 VYG	01/11/2021 00:00
SO0185604	Grays	EJ19 VYG	01/11/2021 00:00
SO0185991	Grays	EJ19 VYG	01/11/2021 00:00
SO0186012	Grays	EJ19 VYG	01/11/2021 00:00
SO0186645	Grays	EJ19 VYG	01/11/2021 00:00
SO0186698	Grays	EJ19 VYG	01/11/2021 00:00
SO0184875	Grays	EU18 YLX	01/11/2021 00:00
SO0185499	Grays	EU18 YLX	01/11/2021 00:00
SO0186323	Grays	EU18 YLX	01/11/2021 00:00
SO0186727	Grays	EU18 YLX	01/11/2021 00:00
SO0186872	Grays	EU18 YLX	01/11/2021 00:00
SO0187113	Grays	EU18 YLX	01/11/2021 00:00
SO0182724	Grays	GO21 HVO	01/11/2021 00:00
SO0182738	Grays	GO21 HVO	01/11/2021 00:00
SO0182787	Grays	GO21 HVO	01/11/2021 00:00
SO0182813	Grays	GO21 HVO	01/11/2021 00:00
SO0182837	Grays	GO21 HVO	01/11/2021 00:00
SO0182852	Grays	GO21 HVO	01/11/2021 00:00

FLEETMASTERLIST

Fleet Name.1	Fleet Name.2	Fleet Truck Daily Cost
Grays	EU66 SYO	£815
Grays	EU67 ZPZ	£815
Grays	EJ19 VYG	£850
Grays	EU18 YLX	£850
Grays	GO21 HVO	£850

How do I create a measure to give me the list of the "Fleet Truck Daily Cost" for the orders that day or for a range of days?

The example for the above Delivery Date should give me a Total for the Depot Grays of £4180.

We have many more Depots and many more Trucks so this would need to work for all orders across multiple dates.

I just can't seem to get the lookup working.

Help please!

[v-chenwuz-msft · Accepted solution]

Hi @DaGemsta ,

Calculate total cost of the special day or a range date.

Please try this code:

Measure =
VAR _S =
    SUMMARIZE (
        ORDERS,
        [Truck],
        [Delivery Date],
        FLEETMASTERLIST[Fleet Truck Daily Cost]
    )
RETURN
    SUMX ( _S, [Fleet Truck Daily Cost] )

Result:

Pbix in the end you can refer.

Best Regards!

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @DaGemsta 

Try to create the following measure:

Total cost = 
var temp_table = summarize( Orders, FLEETMASTERLIST[Fleet Name.2],FLEETMASTERLIST[Fleet Truck Daily Cost])
return
SUMX( temp_table , FLEETMASTERLIST[Fleet Truck Daily Cost])