Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I have given it my best and need some help. Trying to create a freight rating tool. I have a rate table and a shipment history table. I want to compare the costs between different services using the same set of records in the history table. The idea is to select a current service and calculate freight cost, then using the same records of the current selection to calculate the service with a different set of rates based on a different service. I have created a unique id for both tables to link them.
My problem is getting the future service level to calculate using the same recors in the current service level.
Hi Ashish,
Sample Data is below.
So I am trying to rate shipments using service level, FedEx zone, and weight. The tricky part is that I am selecting a current service that is in the dataset and need to rate both current and future services from the same records with the Rate Chart while linking to the Unique ID from both tables. If I select First OverNight and it has 1500 that need rated I also need to rate the same 1500 records for the other service of Priority Overnight or Ground.
So use two dropdown filters and sum both with the respective service to do a comparison on total costs. The difference is the savings if you change services.
Thanks
Doug
Rate Chart
Service | Full Service | Weight | Zone | Disc Rate | Unique ID1 |
FO | First OverNight | 1 | 2 | $15.00 | First OverNight21 |
FO | First OverNight | 2 | 2 | $20.00 | First OverNight22 |
FO | First OverNight | 3 | 2 | $25.00 | First OverNight23 |
FO | First OverNight | 4 | 2 | $30.00 | First OverNight24 |
FO | First OverNight | 5 | 2 | $35.00 | First OverNight25 |
FO | First OverNight | 6 | 2 | $40.00 | First OverNight26 |
FO | First OverNight | 7 | 2 | $45.00 | First OverNight27 |
FO | First OverNight | 8 | 2 | $55.00 | First OverNight28 |
FO | First OverNight | 9 | 2 | $65.00 | First OverNight29 |
FO | First OverNight | 10 | 2 | $70.00 | First OverNight210 |
PO | Priority Ovrnight | 1 | 2 | $5.00 | Priority Ovrnight21 |
PO | Priority Ovrnight | 2 | 2 | $5.25 | Priority Ovrnight22 |
PO | Priority Ovrnight | 3 | 2 | $5.50 | Priority Ovrnight23 |
PO | Priority Ovrnight | 4 | 2 | $5.75 | Priority Ovrnight24 |
PO | Priority Ovrnight | 5 | 2 | $6.00 | Priority Ovrnight25 |
PO | Priority Ovrnight | 6 | 2 | $6.25 | Priority Ovrnight26 |
PO | Priority Ovrnight | 7 | 2 | $6.50 | Priority Ovrnight27 |
PO | Priority Ovrnight | 8 | 2 | $6.75 | Priority Ovrnight28 |
PO | Priority Ovrnight | 9 | 2 | $7.00 | Priority Ovrnight29 |
PO | Priority Ovrnight | 10 | 2 | $7.25 | Priority Ovrnight210 |
Parcel Detail History Table
Unique ID | SERVICE_DESCRIPTION | SHIP_DATE | BILL_WEIGHT | PIECES | ZONE | Current Service Level Charte | Future Service Level Charge |
First OverNight53 | First OverNight | 4/22/2021 5:54:00 PM | 3 | 1 | 5 | ||
First OverNight78 | First OverNight | 4/22/2021 9:48:00 PM | 8 | 1 | 7 | ||
First OverNight54 | First OverNight | 4/22/2021 8:03:00 PM | 4 | 1 | 5 | ||
First OverNight53 | First OverNight | 4/22/2021 7:04:00 PM | 3 | 1 | 5 | ||
First OverNight44 | First OverNight | 4/20/2021 3:47:00 PM | 4 | 1 | 4 | ||
First OverNight32 | First OverNight | 4/21/2021 6:13:00 PM | 2 | 1 | 3 | ||
First OverNight32 | First OverNight | 4/21/2021 6:13:00 PM | 2 | 1 | 3 | ||
First OverNight45 | First OverNight | 4/21/2021 9:20:00 PM | 5 | 1 | 4 | ||
First OverNight54 | First OverNight | 4/22/2021 2:57:00 PM | 4 | 1 | 5 | ||
First OverNight81 | First OverNight | 4/22/2021 3:35:00 PM | 1 | 1 | 8 | ||
First OverNight28 | First OverNight | 4/22/2021 3:48:00 PM | 8 | 1 | 2 | ||
First OverNight41 | First OverNight | 4/22/2021 4:16:00 PM | 1 | 1 | 4 | ||
Priority Ovrnight44 | Priority Ovrnight | 5/13/2021 4:00:00 PM | 4 | 1 | 4 | ||
Priority Ovrnight51 | Priority Ovrnight | 5/13/2021 6:37:00 PM | 1 | 1 | 5 | ||
Priority Ovrnight41 | Priority Ovrnight | 5/13/2021 5:26:00 PM | 1 | 1 | 4 | ||
Priority Ovrnight54 | Priority Ovrnight | 5/13/2021 3:36:00 PM | 4 | 1 | 5 | ||
Priority Ovrnight54 | Priority Ovrnight | 5/13/2021 4:00:00 PM | 4 | 1 | 5 | ||
Priority Ovrnight54 | Priority Ovrnight | 5/13/2021 4:08:00 PM | 4 | 1 | 5 | ||
Priority Ovrnight44 | Priority Ovrnight | 5/13/2021 4:07:00 PM | 4 | 1 | 4 | ||
Priority Ovrnight49 | Priority Ovrnight | 5/13/2021 4:53:00 PM | 9 | 1 | 4 | ||
Priority Ovrnight54 | Priority Ovrnight | 5/13/2021 4:39:00 PM | 4 | 1 | 5 | ||
Priority Ovrnight48 | Priority Ovrnight | 5/13/2021 7:45:00 PM | 8 | 1 | 4 | ||
Ground45 | Ground | 4/2/2021 2:10:00 PM | 5 | 1 | 4 | ||
Ground29 | Ground | 4/2/2021 3:21:00 PM | 9 | 1 | 2 | ||
Ground27 | Ground | 4/6/2021 10:05:00 AM | 7 | 1 | 2 | ||
Ground53 | Ground | 2/9/2021 2:44:00 PM | 3 | 1 | 5 | ||
Ground54 | Ground | 3/8/2021 12:40:00 PM | 4 | 1 | 5 |
Hi,
I do not understand your requirement. Someone who does will surely help you.
Hi,
Share some data and show the expected result.
Hmm, just to be clear, you would want to first grab the data where service level equals current service level.
Then on this subset, filter those that have service level equal future service level slicer?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |