locked
Max of Sum ... RRS feed

  • Question

  • Hello,

    I am in transportation and have one challenge to create a measure.

    The user is evaluating different trip based on his selections.

    Each trip contains many stops.  For every stop, we count the number of people that get in the vehicule and the number of people getting out (but let's forget about this one).

    For each trip, it's easy to have the maximum number of people that got in the vehicule using Measure:=Max(PeopleIn).

    Now, I also would like to have a measure that identify what was the total number of people getting in the vehicule, again, quite easy through Measure:=Sum(PeopleIn).

    The challenge I'm facing right now is that I would like to define a MAX, MIN and AVERAGE measure of that Sum(People) based on the user selection.

    Basically, my fact table looks like this:

    TripId, TripDateId, StopId, PeopleIn

    A, 2016-01-01, A1, 2

    A, 2016-01-01, A2, 0

    A, 2016-01-01, A3, 1

    A, 2016-01-02, A1, 2

    A, 2016-01-02, A2, 2

    A, 2016-01-02, A3, 2

    A, 2016-01-03, A1, 5

    A, 2016-01-03, A2, 1

    A, 2016-01-03, A3, 1

    So bascially, if the user use some filter and ends up with Trips done on 1st and 2nd of January, MAX measure would be 6 because the maximum total number of people in is on Trip from January 2nd.

    MIN would be 3 (Trip of January 1st) 

    AVERAGE would be 4.5 (6+3 / 2 trips on the selection)

    Any idea how to achieve this?

    Thank you.


    Friday, July 8, 2016 6:01 PM

Answers

  • What you're looking for, is a table aggregation over a table of trips. Based on your fact table, a table of trips (in the context set by your filters) can be obtained with VALUES(fact[TripID]), but if you have a separate Trips table linked to the fact table you can do the same with the Trips table, e.g. VALUES(Trips[TripID]). The total number of people can be obtained, as you already stated, by SUM(Fact[PeopleIn]), let's call this measure [TotalPeople].

    Now, your max, min, and average are calculated using MAXX, MINX, and AVERAGEX, respectively. For instance, the maximum number of people by trip:

    MaxByTrip:=MAXX(VALUES(fact[TripID]);[TotalPeople])

    the minimum number:

    MinByTrip:=MINX(VALUES(fact[TripID]);[TotalPeople])

    and the average number:

    AvgByTrip:=AVERAGEX(VALUES(fact[TripID]);[TotalPeople])

    • Marked as answer by __Erik__ Friday, July 8, 2016 7:29 PM
    Friday, July 8, 2016 7:07 PM
    Answerer

All replies

  • What you're looking for, is a table aggregation over a table of trips. Based on your fact table, a table of trips (in the context set by your filters) can be obtained with VALUES(fact[TripID]), but if you have a separate Trips table linked to the fact table you can do the same with the Trips table, e.g. VALUES(Trips[TripID]). The total number of people can be obtained, as you already stated, by SUM(Fact[PeopleIn]), let's call this measure [TotalPeople].

    Now, your max, min, and average are calculated using MAXX, MINX, and AVERAGEX, respectively. For instance, the maximum number of people by trip:

    MaxByTrip:=MAXX(VALUES(fact[TripID]);[TotalPeople])

    the minimum number:

    MinByTrip:=MINX(VALUES(fact[TripID]);[TotalPeople])

    and the average number:

    AvgByTrip:=AVERAGEX(VALUES(fact[TripID]);[TotalPeople])

    • Marked as answer by __Erik__ Friday, July 8, 2016 7:29 PM
    Friday, July 8, 2016 7:07 PM
    Answerer
  • Works great!  Thanks a lot!
    Friday, July 8, 2016 7:29 PM