cancel
Showing results for
Did you mean:
Resolver II

Returning SUM of MAX duration for bus trips to calculate total duration

Note - I want to do this in a measure, rather than a calculated column.

My data set comprises stop level data for bus routes, with timings, as well as a calculated column that calculates total elapsed duration. E.g. the following, where the result should be 840:

 UniqueTripID Stop Time RunningTimeDuration (calc column) A 1 7:00:00am 0 A 2 7:02:00am 120 A 3 7:04:00am 240 A 4 7:06:00am 360 B 1 8:00:00am 0 B 2 8:03:00am 180 B 3 8:05:00am 300 B 4 8:08:00am 480

The total time for a trip is the MAX value for Calculated Duration for any given UniqueTripID (these are unique identifiers )

I've come up with a measure that works for individual level routes:

var routeresult =
CALCULATE(
MAX(
)

What I want to do is SUM the MAX for every unique TripID. The following does not work:
//CALCULATE(
// SUM(
//)
1 ACCEPTED SOLUTION
Super User
``````Max Cumul :=
return sumx(a,[maxlen])``````

3 REPLIES 3
Super User
``````Max Cumul :=
return sumx(a,[maxlen])``````

Resolver II

Wonderful, works perfectly. Here is your result compared to one I created using a helper column (which slows down calculation time), and the difference is infinitesimal.
Thanks so much!!!

Super User

you make a good point, especially on the impact of larger data sets on the performance. Instead of SUMMARIZE you can also use GROUPBY() or a variety of other approaches. In DAX Studio you can study the query plan and optimize your query as needed.

Announcements

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!