Distance Bins based on long and lat

[Magpie_Rob]

Hello

So I have 2 tables, on with customers and there Longitude and Latitude and anotherone with Stores with there longitude and latitude - I'm trying to create a table with a list of the stores and how many customers fall withing certain distance (in bins). (5miles, 10miles, 15 miles) etc. I have included an excel table of how I would expect it to look.

I found a measure created by RADCAD which figures out the distance but I can't figure out how to make this work across mutilple stores and count it plus put it into bins. any help is greatly appricated. - I included a sample Pbix as well. 

https://1drv.ms/u/s!ApUZenl1efyn03AHr5mVd-BZI1Tk?e=Wgos2m

Thanks 

Magpie

[parry2k · Accepted solution]

@Magpie_Rob is this what you are looking for:

[parry2k]

@Magpie_Rob I'm so so sorry about this. I totally messed up. Thanks for keep on following up. Filesize is bigger than the size I can attach here, let me know how else I can send it to you.

[Magpie_Rob]

Hi @parry2k  no worries at all! everyone is busy! I have sent you a direct message with my email

[parry2k]

@Magpie_Rob oh! I'm so sorry for not getting back to you. Thanks for the nudge. I will send you the pbix file as soon as I'm in front of the computer.

Stay tuned!

[Magpie_Rob]

Hi @parry2k I hope you are well, at the risk of sounding really desperate could you please share the Pbix with me please ? 

Thank you! 

[Magpie_Rob]

Hi @parry2k I hope you are well,

I fear you may have forgotten me again 🙈 at the risk of sounding desperate could you please share the Pbix with me please ? 

Thank you! 

[parry2k · Accepted solution]

@Magpie_Rob is this what you are looking for:

[Magpie_Rob · Accepted solution]

This was the main bit! it worked really well thank you! 

Store Distance Bins = 
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( Store_Location[Postcode] ),
        VALUES ( 'Customers Locations'[id] )
    ),
    "Miles", [Miles],
)

[Magpie_Rob]

@parry2k yeah thats exactly what I'm looking for! are you able to share the pbix or let me know how you achived it? 

[Magpie_Rob]

Hi @parry2k just checking you haven't forgot about me! the soultion to this would be very much appricated! 

Thanks