cancel
Showing results for
Did you mean:
Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

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

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]```
```Measure_Origin = CALCULATE(COUNT(Routes[Origin]),INTERSECT(ALL(Routes[Origin]),VALUES(Cities[Cities])))