cancel
Showing results 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.

2 REPLIES 2
Employee

Hi @cweylan,

Could you please share some sample data to have a test and post your desired result if possible?

Regards,

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.
Frequent Visitor

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.

Announcements

#### 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.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors