Calculating Supported Trips Based on Area and Direction in Power BI

[twowaysyellow]

I have a table with information about trips, and whether a trip is supported or not based on whether it takes place within a certain area. A trip is supported either by the departing (starting) area, the arriving (end) area or not supported at all.

Not all areas are supporters and trips can take place in unsupported areas. A trip can only be supported by one area.

Trips can travel within the same are or between different areas.

Trips within the same area can be either

1. supported or
2. not supported

For trips that travel from one area to another there are several scenarios

1. both from and to areas are supporters
2. only from is supporter
3. only to is supporter
4. none are supporters

For 1 and 2 in different areas, it is always the from area that is considered the supporter. In scenario 3 the to area is considered supporter and for 4 obviously none are supporters.

I have a base table like this

trip_id	direction	area_id	from_supported	to_supported
1	from	1	1	0
1	to	2	0	1
2	from	3	0	0
2	to	1	0	1
3	from	1	1	0
3	to	3	0	0
4	from	3	0	0
4	to	4	0	0
5	from	1	1	0
5	to	1	0	1
6	from	3	0	0
6	to	3	0	0

I want to end up with a table like the one below. I already have the first 5 columns, but I'm missing the last one, which I cannot get to work. I hope someone can help.

area_id	trips_starting	trips_ending	trips_within	total_trips	trips_supported
1	2	1	1	4	4
2		1		1	0
3	2	1	1	4	0
4		1		1	0

[whitch]

In Power Query create a 'Trips' table by grouping your base table on Trip_id and area_id, and summing the [from_supported] and [to_supported] columns.  Add a [trip supported] column which is 0 if [from_supported] and [to_supported] are both 0, and is 1 otherwise.  Now group on area_id and sum [trip supported].