Make Calculated Table of Stops that looks at both Pickup and Delivery Stop on an Order

Question

I do Power BI for a logistics company. We want to show performance by stop location. The data is currently a table of all orders by Order ID, so -- ID, Rev $, Pickup Stop, Delivery Stop. Everything is a 2-stop load, fortunately.

What I am struggling with is building a calculated table that looks at the Pickup Stop AND the Delivery Stop at the same time while ALSO respecting filters set on the page. I would like the stops table to say something like: Stop Location, X Pickups, $X Pickup Revenue, X Deliveries, $X Delivery Revenue.

How would I go about this? I've tried a number of approaches but every time it either misses filters or can only handle one stop at a time.

Thanks!

Current Data
call it Orders

The calculated table I'm trying to make
call it Stops

Answer 1

One method of creating your Stops, given your Orders is by using Power Query, accessed via Queries=>Transform Data on the Power BI Home Tab.

The Table.Group function is where the magic happens. Unfortunately, it needs to be done by coding in the Advanced Editor, as the UI does not provide for these custom aggregations.

• When the PQ Editor opens: Home => Advanced Editor
• The first three lines should be replaced by whatever you are reading in your own Orders table with.
• Paste the rest of M Code below in place of what is below your setup lines in your own query
• Read the comments and explore the Applied Steps to understand the algorithm

M Code

let
    //Input data and set datatypes
    //These lines should be replaced with whatever you need to 
    //set up your data table
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYzBCoMwEER/Zck5BxPRu1RaLJaW6qEQPIS4tEExoonQv+/a0oLQyw5vZnaUYuepxQmKnHFWO697uOKCQ0DiizVdGKHybiTKsbcLTs8PN1wxIZMooiR938z3evCawyFbKczeDhzq268qyBZpsg23f9+qJF+Skuwe1ui741CU/2djsmO53lJ3SFsth/3aPWrTzY7Kp4o1zQs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    dataSource = Table.TransformColumnTypes(#"Promoted Headers",{
        {"Order ID", Int64.Type}, {"Total Revenue", Int64.Type}, 
        {"Pickup Stop", type text}, {"Delivery Stop", type text}}),

    //Unpivot to get single column of Stops
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(dataSource, {"Order ID", "Total Revenue"}, "Attribute", "Stop"),

    //Group by stop and do the aggregations
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Stop"}, {
        {"Orders Picked Up", (t)=> List.Count(List.Select(t[Attribute], each _ = "Pickup Stop" )), Int64.Type},
        {"Total Revenue Picked Up", (t)=> List.Sum(Table.SelectRows(t, each [Attribute]="Pickup Stop")[Total Revenue]), type number},
        {"Orders Delivered", (t)=> List.Count(List.Select(t[Attribute], each _ = "Delivery Stop" )), Int64.Type},
        {"Total Revenue Delivered", (t)=> List.Sum(Table.SelectRows(t, each [Attribute]="Delivery Stop")[Total Revenue]), type number}
        })
in
    #"Grouped Rows"

Orders

Stops