Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]
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]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |