Finding top 3 averages across range of journeys with specific end locations

[sclennell]

OK, slightly complex query and I'm a complete PowerBI newbie, so not great with this!

I have source data as follows:

Start Location    End Location    Time

A                        H                      00:01

B                        J                       02:00

C                        I                       03:03

D                        Z                      01:01

B                       I                        02:00

The start and end locations are specifc unique locations (different lists from each other - we have a scenario where devices travel from a pool of locations to a different pool of locations).

I have approx 100,000 rows of this data, all based on captured data.

I am trying to get a table that would give me the following:

End Location (unique list)                  Quickest average start location  (Time)                2nd quickest average start location  (Time)          3rd...

I also need to exclude the quickest, 2nd quickest and 3rd quickest if they only had 2 entries or less as outliers.

I cant work out where to start! Any suggestions greatly appreciated.

[v-ljerr-msft · Accepted solution]

Hi @sclennell,

If I understand you correctly, you should be able to follow steps below to get your expected result.

I assume your called is "Table1".

1. Use the formula below to create a new table with each End Location and distinct Time values.

New Table = 
SUMMARIZE (
    Table1,
    Table1[Start Location],
    Table1[End Location],
    "Averate Time", AVERAGE ( Table1[Time] )
)

2. Use the formula below to add a calculate column to the new created table to Rank Time values for each End Location.

Rank = 
VAR t = 'New Table'[Averate Time]
VAR endL = 'New Table'[End Location]
RETURN
    CALCULATE (
        RANK.EQ ( t, 'New Table'[Averate Time], ASC ),
        FILTER ( ALL ( 'New Table' ), 'New Table'[End Location] = endL )
    )

3. Then should be able to use the formula below to create a new table with End Location (unique list), Quickest average start location  (Time), 2nd quickest average start location  (Time), 3rd...

Final Table = 
SUMMARIZE (
    'New Table',
    'New Table'[End Location],
    "Quickest average start location", CALCULATE ( FIRSTNONBLANK('New Table'[Start Location],1), 'New Table'[Rank] = 1 ) ,
    "2nd quickest average start location", CALCULATE ( FIRSTNONBLANK('New Table'[Start Location],1), 'New Table'[Rank] = 2 ),
    "3rd quickest average start location", CALCULATE ( FIRSTNONBLANK('New Table'[Start Location],1), 'New Table'[Rank] = 3 )
)

4. When you show the table on the report, you can apply a visual level filter(2nd quickest average start location is not blank, 3rd quickest average start location is not blank) to exclude the quickest, 2nd quickest and 3rd quickest if they only had 2 entries or less as outliers. 

Regards

[sclennell]

OK, slightly complex query and I'm a complete PowerBI newbie, so not great with this!

I have source data as follows:

Start Location    End Location    Time

A                        H                      00:01

B                        J                       02:00

C                        I                       03:03

D                        Z                      01:01

B                       I                        02:00

The start and end locations are specifc unique locations (different lists from each other - we have a scenario where devices travel from a pool of locations to a different pool of locations).

I have approx 100,000 rows of this data, all based on captured data.

I am trying to get a table that would give me the following:

End Location (unique list)                  Quickest average start location                  2nd quickest average start location            3rd...

I also need to exclude the quickest, 2nd quickest and 3rd quickest if they only had 2 entries or less as outliers.

I cant work out where to start! Any suggestions greatly appreciated.