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
cweylan
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
v-danhe-msft
Employee
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.

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

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.