Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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])
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |