Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pirtlede
Frequent Visitor

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

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.

pirtlede_0-1629492293225.png

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
4 REPLIES 4
pirtlede
Frequent Visitor

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

ServiceFull Service WeightZoneDisc RateUnique ID1
FOFirst OverNight12$15.00First OverNight21
FOFirst OverNight22$20.00First OverNight22
FOFirst OverNight32$25.00First OverNight23
FOFirst OverNight42$30.00First OverNight24
FOFirst OverNight52$35.00First OverNight25
FOFirst OverNight62$40.00First OverNight26
FOFirst OverNight72$45.00First OverNight27
FOFirst OverNight82$55.00First OverNight28
FOFirst OverNight92$65.00First OverNight29
FOFirst OverNight102$70.00First OverNight210
POPriority Ovrnight12$5.00Priority Ovrnight21
POPriority Ovrnight22$5.25Priority Ovrnight22
POPriority Ovrnight32$5.50Priority Ovrnight23
POPriority Ovrnight42$5.75Priority Ovrnight24
POPriority Ovrnight52$6.00Priority Ovrnight25
POPriority Ovrnight62$6.25Priority Ovrnight26
POPriority Ovrnight72$6.50Priority Ovrnight27
POPriority Ovrnight82$6.75Priority Ovrnight28
POPriority Ovrnight92$7.00Priority Ovrnight29
POPriority Ovrnight102$7.25Priority Ovrnight210

 

Parcel Detail History Table

Unique IDSERVICE_DESCRIPTIONSHIP_DATEBILL_WEIGHTPIECESZONECurrent Service Level CharteFuture Service Level Charge
First OverNight53First OverNight4/22/2021 5:54:00 PM315  
First OverNight78First OverNight4/22/2021 9:48:00 PM817  
First OverNight54First OverNight4/22/2021 8:03:00 PM415  
First OverNight53First OverNight4/22/2021 7:04:00 PM315  
First OverNight44First OverNight4/20/2021 3:47:00 PM414  
First OverNight32First OverNight4/21/2021 6:13:00 PM213  
First OverNight32First OverNight4/21/2021 6:13:00 PM213  
First OverNight45First OverNight4/21/2021 9:20:00 PM514  
First OverNight54First OverNight4/22/2021 2:57:00 PM415  
First OverNight81First OverNight4/22/2021 3:35:00 PM118  
First OverNight28First OverNight4/22/2021 3:48:00 PM812  
First OverNight41First OverNight4/22/2021 4:16:00 PM114  
Priority Ovrnight44Priority Ovrnight5/13/2021 4:00:00 PM414  
Priority Ovrnight51Priority Ovrnight5/13/2021 6:37:00 PM115  
Priority Ovrnight41Priority Ovrnight5/13/2021 5:26:00 PM114  
Priority Ovrnight54Priority Ovrnight5/13/2021 3:36:00 PM415  
Priority Ovrnight54Priority Ovrnight5/13/2021 4:00:00 PM415  
Priority Ovrnight54Priority Ovrnight5/13/2021 4:08:00 PM415  
Priority Ovrnight44Priority Ovrnight5/13/2021 4:07:00 PM414  
Priority Ovrnight49Priority Ovrnight5/13/2021 4:53:00 PM914  
Priority Ovrnight54Priority Ovrnight5/13/2021 4:39:00 PM415  
Priority Ovrnight48Priority Ovrnight5/13/2021 7:45:00 PM814  
Ground45Ground4/2/2021 2:10:00 PM514  
Ground29Ground4/2/2021 3:21:00 PM912  
Ground27Ground4/6/2021 10:05:00 AM712  
Ground53Ground2/9/2021 2:44:00 PM315  
Ground54Ground3/8/2021 12:40:00 PM415  

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Kucrapok
Helper I
Helper I

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?

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors