Creating a measure that can calculate 2 dynamically filtered values

[DCPics]

I have a problem a couple of us have been trying to solve for a few days now.  We have multiple manufacturing locations and when we plan a part to be built, we have a "Planned WH", but that isn't always what happens in reality.  We also log the "Shipping WH", and planned may not always match the shipped.

I have a BI dashboard created that looks at how many pieces we planned for certain wharehouses, and that data is mapped, and has a card that shows the number of pieces.  I also have another map which shows our shipping warehouse and all shipments that were sent within 500 miles of that location.

I am trying to come up with a measure that can tell me the difference in the two metrics.

In the example I attached, you'll see the Planned WH and sales (Count) come from the "Sales" table.  I also have a table with distances from each warehouse called "Zip Miles" and that one is joined on Shipping WH so I can see how far it was to our customer.

If you look at Planned WH 1, we planned for 22 units to be shipped out of there, but only 17 went to customers within 500 miles.

I need a measure that can subtract those two values and tell me that we were off plan by 5 units.....or in the Planned WH 2 example shown, we were off by 10 units.

Thank you very much for your help with this, it's been driving me nuts for days.  

[v-lili6-msft]

hi, @DCPics 

How to calculate Shipping WH(500) " but only 17 went to customers within 500 miles"

I think you should create a WH fact table, then create relationships with Sales and Zip Miles, respectively.

Then drag WH field from WH fact table and new measure [planned wh count] - [shipping wh count].

If it is not your case, please share simple pbix file or some data sample and expected output. Do mask sensitive data before uploading.

Best Regards,

Lin

[DCPics]

I am still having problems.  I am running into an issue with it being an Many to Many relationship.  The problem is apparently that we can ship orders incomplete, and then send another delivery later.  So, I tried what you suggested, and created a key of order number, line number, qty, and ship date, but I am still getting multiples.

I'm going to give something a try today and will post here if it works.

Thank you.

[DCPics]

FYI, the 500 miles can be hard coded, we are only looking for shipments within 500 miles of our shipping locations.

Thank you again for your help with this!