Use dropdown slicers to calculate measures on the some table records.

[pirtlede]

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.

Current Disc Rate = CALCULATE(SUM('Rate Chart',[Disc Rate]),FILTER('FedEx Rate Chart','FedEx Rate Chart'[Full Service ] = SELECTEDVALUE(Parcel_May_and_June_2021[Current Service Level])))

Future Disc Rate = CALCULATE(SUM('Rate Chart'[Disc Rate]),FILTER('FedEx Rate Chart','FedEx Rate Chart'[Full Service ] = SELECTEDVALUE(Parcel_May_and_June_2021[Current Service Level])))

All I get back is the sum of the rates for each service based on the rates in the Rate Chart and not the historical shipment table.

Any help is appreciated.

Thanks

Doug

[pirtlede]

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

[Ashish_Mathur]

Hi,

I do not understand your requirement.  Someone who does will surely help you.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Kucrapok]

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?