How to calculate the IN and OUT flow of a city that works with slicer?

[ioncarmona]

Hello all, I am quite newby in PowerBI so this question might be quite simple for the experts.

I would like to calculate the balance of the traffic flow of a city, I mean calculate how many INs and OUTs I have got at each city.

My inputs are as follows. I have got a list of routes which connects the Origin city with the Destination City. (Table name: Routes)

ID	Origin	Destination	Class	Priority
1	Madrid	London	A	High
2	Madrid	Berlin	B	Medium
3	Madrid	Berlin	A	High
4	Madrid	Berlin	B	Medium
5	Madrid	Berlin	A	High
6	Madrid	Boston	B	Medium
7	Boston	Madrid	A	High
8	London	Detroit	B	Medium
9	Berlin	Detroit	A	High
10	Boston	Detroit	B	Medium
11	London	Madrid	A	High
12	Boston	Detroit	B	Medium
13	Paris	Boston	A	High
14	London	Detroit	B	Medium
15	Boston	Detroit	A	High
16	Boston	Detroit	B	Medium
17	London	Detroit	A	High
18	Boston	London	B	Medium
19	San Francisco	Tokyo	A	High
20	Delhi	Canberra	B	Medium

I created a lookup table with the name of all cities so calculate the balance. The outcome was as follows: (LookUp table name: Cities)

Cities	Column_Origin	Column_Destination	Column_Balance
Madrid	6	2	4
Boston	6	2	4
London	4	2	2
Berlin	1	4	-3
Paris	1		1
San Francisco	1		1
Delhi	1		1
Detroit		8	-8
Tokyo		1	-1
Canberra		1	-1

The formula that I used to set the Columns were the following:

Column_Origin = calculate(COUNT(Routes[Origin]);filter(Routes;Routes[Origin]=Cities[Cities]) )

Column_Destination = calculate(count(Routes[Destination]);filter(Routes;Routes[Destination]=Cities[Cities]))

Column_Balance = Cities[Column_Origin]-Cities[Column_Destination]

This worked fine, but when I applied the slicer such us Class or Priority, it did not work as I expected. The slicer updated the list of the cities but the values remained the same.

In order to solve this issue, I tried to replace Calculated Columns for Measures. However, I was having some issues to create the measure.

Please, does anyone could advice on this. This would be very much appreciated.

Thanks to the community

Ion

[Stachu · Accepted solution]

this will work, you still need Cities table (but without calculated columns)

Measure_Origin = CALCULATE(COUNT(Routes[Origin]),INTERSECT(ALL(Routes[Origin]),VALUES(Cities[Cities])))

Measure_Destination = CALCULATE(COUNT(Routes[Destination]),INTERSECT(ALL(Routes[Destination]),VALUES(Cities[Cities])))

Measure_Balance = [Measure_Origin]-[Measure_Destination]