cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ioncarmona Visitor
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)

 

IDOriginDestinationClassPriority
1MadridLondonAHigh
2MadridBerlinBMedium
3MadridBerlinAHigh
4MadridBerlinBMedium
5MadridBerlinAHigh
6MadridBostonBMedium
7BostonMadridAHigh
8LondonDetroitBMedium
9BerlinDetroitAHigh
10BostonDetroitBMedium
11LondonMadridAHigh
12BostonDetroitBMedium
13ParisBostonAHigh
14LondonDetroitBMedium
15BostonDetroitAHigh
16BostonDetroitBMedium
17LondonDetroitAHigh
18BostonLondonBMedium
19San FranciscoTokyoAHigh
20DelhiCanberraBMedium

 

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

 

CitiesColumn_OriginColumn_DestinationColumn_Balance
Madrid624
Boston624
London422
Berlin14-3
Paris1 1
San Francisco1 1
Delhi1 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
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]
1 REPLY 1
Highlighted
Super User
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]