Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have a calculated measure named "Miles". This takes the longitude of a home address and measures the distance to the stores located near it. However, I want to be able to calculate whether the store that the delivery occured from is actually the closest store. I have the store number that the order was shipped from. For example, in table A I have customer lat/long and the store that it was shipped from. In table B, I have all stores lat/longs. I then created a Miles formula see below:
Miles =
var Lat1 = MIN('Ex Delivery Task Data'[Postal Codes.LAT])
var Lng1 = MIN('Ex Delivery Task Data'[Postal Codes.LNG])
var Lat2 = MIN('Distance From Stores'[Latitude])
var Lng2 = MIN('Distance From Stores'[Longitude])
---- Algorithm here -----
var P = DIVIDE( PI(), 180 )
var A = 0.5 - COS((Lat2-Lat1) * p)/2 +
COS(Lat1 * p) * COS(lat2 * P) * (1-COS((Lng2- Lng1) * p))/2
var final = 7919.2 * ASIN((SQRT(A)))
return final
But this only will work when I click on a customer value (which is what I want). However, I want to create another measure that calculates whether the order was shipped from the closest store, but I do not want to have to select a specific customer order. I want to see the general population.
Hi @cweylan,
Could you please share some sample data to have a test and post your desired result if possible?
Regards,
Daniel He
This is a sample of my store data.
This is a sample of my customer data.
This is what I want to create. So I want to see if the customer order was shipped from the closest store based on longitude and latitude cordinates.
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
189 | |
96 | |
67 | |
63 | |
53 |