Busses and capacities challenge

[kalcey]

A bus has 5 runs a day (at 8:00, at 8:15, 8:30, 8:45, 9:00, & 10:00 )

Said bus can fit 15 people.

A 6th run can fit 20 people.

The bus has also 6 pick up stops (say Greenwood, Greenlake, Fremont, Seattle, Georgetown, Tacoma)

A log file tracks every time the bus picks a person. For example:

• Day 1 ... Run1   ....   Greenwood ... 1 pick up
• Day 1 ... Run1   ....   Greenlake    ... 1 pick up
• Day 1 ... Run2   ....   Freemont ... 1 pick up
• Day 2 ... Run2   ....   Seattle ...      1 pick up
• Day 3 ... Run3   ....   Tacoma       ... 1 pick up

Calculating Sum(PickUps) is easy...

What seems to be mission impossible is calculating the Capacity of each bus, grouped by stop, when no passengers are picked at said stop. 

For example, if day 2 / run 1 does not stop at Greenlake, we get this:

• Day 1 ... Run1   ....   Greenwood ... 3 pick ups, Capacity=15
• Day 1 ... Run1   ....   Greenlake   ... 4 pick ups, Capacity=15
• Day 1 ... Run1   ....   Freemont    ... 5 pick ups, Capacity=15
• Day 2 ... Run1   ....   Greenwood ... 2 pick ups, Capacity=15
• Day 2 ... Run1   ....   Freemont    ... 7 pick ups, Capacity=15

Aggregating the data leads to the following:

• Month 1 ... Run1   ....   Greenwood ... 3+2 pick ups, Capacity=15*2
• Month 1 ... Run1   ....   Greenlake   ... 4      pick ups, Capacity=15*1
• Month 1 ... Run1   ....   Freemont    ... 5+7 pick ups, Capacity=15*2

The first 3 records belong to the same bus/run, hence the capacity should be calculated once.

My solution (that did not work) was create a table with all runs per day, and get the capacity from there, to avoid adding the capacity at every stop, but when no pickup takes place, it doesn't fetch the capacity information to add it to its sum, even when I created a cross-join table with all stops associated w/ a run.

I ended up with that:

MaxSeatCapacity Sum_ = SUMX(
                            SUMMARIZE(
                                'Runs & Dates',
                                [ID],
                                "UniqTrips",
                                AVERAGE(Trip[Capacity])
                            ),
                            [UniqTrips] )

Explanation: Look for unique runs (a combination of dates and runs),
and get the sum of the capacities for these runs (the average is to simulate aggregation, all data points are the same for a date-run combination)

Thank you!

[v-huizhn-msft]

Hi @kalcey,

The date in your resource table is day level? If it is, I try to reproduce your scenario using the following sample data, and get expected result.

You’d better create measures to calculate the number of pick up passengers, and max capacity of each run in one day. Create a table, it will display the factual pick up and max seat for a combination of dates and runs.

sum of run/day = CALCULATE(SUM(Table5[number pick up]),ALLEXCEPT(Table5,Table5[Date]))
MAX = CALCULATE(SUM(Table5[Capacity]),ALLEXCEPT(Table5,Table5[Date]))

If you want to calculate the max seats of run1 in month, you’d better add an month, count column and total capacity calculated column, then create a table without summarize the field.

Month = MONTH(Table5[Date])
count1 = CALCULATE(COUNTA(Table5[Stops]),ALLEXCEPT(Table5,Table5[Month],Table5[Run],Table5[Stops]))
capacity1 = Table5[Capacity]*Table5[count1]

IF this is not what you want, please post the sample data for further analysis.

Best Regards,
Angelia

[kalcey]

Thanks for your elaborate help @v-huizhn-msft,

You got it correctly, date is on a day level, stops, pickups, ...

I see that you ran into the same problem as I did:

It is the same bus that visits all stops, so we know that since there are 2 runs on Greenwood, then it follows that the others stops should have the same number. In your case, Greenlake has a count of 1.

Thoughts?

[v-huizhn-msft]

Hi @kalcey,

>>we know that since there are 2 runs on Greenwood, then it follows that the others stops should have the same number. In your case, Greenlake has a count of 1.

Althrough there are 2 runs on Greenwood, but they are in different dates, why others stops should have the same number?

Best Regards,
Angelia

[kalcey]

In your example, you have the following:

  1/2/2016  Run1 Greenwood

  1/2/2016  Run1 Freemont

We know that the list of stops for the same bus is: Greenwood, Greenlake, Fremont, Seattle, Georgetown, Tacoma.

Consequently, If one bus logs 2 passengers at Greenwood, it follows that the bus passed through all other stops, even if no passengers were marked in the data.