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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Showing Top N values per category based on a value

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?



Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Capture.PNGThis is a sample of my store data.

Capture.PNGThis is a sample of my customer data.

Capture.PNGThis 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.

Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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