Calculating sum of locations in neighbouring postcodes

[masplin]

I am doing some analysis of restaurant density. i have create a list of restaurants and their postcode district (the AL1 bit). i have easily counted how many competitors to each restaurant there are in the SAME postcdoe district using a calcualted column

=CALCULATE(
 [Count],
 FILTER(
 Restaurants,
 Restaurants[Postcode District]=EARLIER(Restaurants[Postcode District]) &&
 Restaurants[Chain]<>EARLIER(Restaurants[Chain])
 )
 )

Now i want to try to count how many others are in neighbouring districts. i have created another table caleld Postcode_NearbyDistricts that contains 2 columns, firstly the district then the neighbouring district. so for each district there might be say 10 rows. for example for District L2

Postcode District Nearby District

L2	CH25
L2	L1
L2	L3
L2	L5
L2	L67
L2	L69
L2	L70
L2	L71
L2	L73
L2	L74

My restaurant table looks like this

Branch Postcode District

Liverpool	L1
Albert Dock	L3
Aughton	L39
Otterspool	L17
Liverpool	L1
Liverpool	L1
Liverpool	L2
Liverpool	L1
Liverpool	L37

So for a row on the restaurant table i need to look up the nearby district rows and then filter the restaurant table for the 10 that match and count the rows.  i just can't work out how to do this if anyone can help. There are multiple districts on both tables so cant use any relationship

Much appreciated

Mike

[v-danhe-msft]

Hi @masplin,

Based on my test, you can refer to below steps:

1.I have entered some sample data to test for your problem in below pictures.

2.Create a calculated column to count the relative district.

Related district number = COUNTROWS(FILTER(RELATEDTABLE('Postcode District Nearby Distri'),Restaurant[District]='Postcode District Nearby Distri'[District]))

3.Create a Table visual and add the [Name], [District] and [Related district number] field and you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/geu61x1iv20wqa4/Calculating%20sum%20of%20locations%20in%20neighbouring%20p...

Regards,

Daniel He

[masplin]

Sorry maybe wasnt clear as problem is more complicated than that. In my example I have one restaurant that is in L2

L2 has 10 neighbouring postcode districts. Now i need to take these 10 districts and go back to the restaurant table and count how many are in those. so in my example the restaurants in L1 & L3 are neighbouring and there are 5 of those. The restaurants in L2,L39, L17 and L37 are no neighbouring so dont count.  The answer is therefore 5.

I tried this code, but it is a circular reference, but logically seems sort of right process

In middle CALCULATE I tried to get a table of values of the nearby districts for the specific row I was on.

Then FILTER the restaurants table for just the districts in the table found from the NearbyDistricts table that are related

Then SUM the [count] (just a row count measure) over the fitlered table

=CALCULATE(
            SUMX(
                 FILTER(Restaurants,
                 [Postcode District]=
                  CALCULATE(
                            VALUES(Postcode_NearbyDistrict[Nearby District]),
                            Restaurants[Postcode District]=Postcode_NearbyDistrict[Postcode district]
                            )
                      )  ,
                   [Count]                           
                   ))

I have hacked this together in excel usingh a terrible method.  I have all my restaurants down the side and all the possible postcode districts with neighbouring ditricts underneath along the top.  Massive matrix looking up each restaurants related districts then a formula in each column to go and count how many for each of the related ditricts. Then sum the row. It works but its rubbish

formula is =IF($D4=F$2,SUMIF($D:$D,F$3,$E:$E),"").  First column is D, first row is 2. so if Restaurant district = top row district then use the 2nd row of ditricts to sum the count column where the districts match

Postcode DistrictCount

		AB10	AB10	AB10	AB10
		AB1	AB11
W1B	1
NW3	1
SW3	1
EC3V	1

One question is how come you can use RELATEDTABLE when it is a Many to Many relationship?  Is this because as a clauted column the relationship is context filtered to only have one value coming from the Restaurant table?  if so that would mean your idea of RELATEDTABLE gnerates the table of values needed to feedback to the restaurant table. I suspect you still get a circular reference.

Mike