Group then Distinct Count then Average using DAX

[RichardJChester]

I have a need to analyze the Average Number of Shipments per Order per Day on an imported table as a DAX measure.  I need to use a measure vs. a derived table because my users need to be able to slice the data prior to the metric being calculated.  I have a solution that works, but it is show to present data in my visuals.  I wonder if there's a more efficient way to do this - any ideas will be most appreciated!

The final analysis shows the average number of shipments per order by order date.  

For example, I might have 3 orders placed on June 23.   Here is a simplified view of my data.

Order Number     Ordered On Date     Shipped On Date   Branch

1                           6/23/2023                6/25/2023              A1

1                           6/23/2023                6/25/2023              A2

1                           6/23/2023                7/1/2023                A1

1                           6/23/2023                7/1/2023                B1

2                           6/23/2023                7/8/2023                A1

2                           6/23/2023                7/8/2023                B2

3                           6/23/2023                7/1/2023                B1

3                           6/23/2023                7/5/2023                B1

3                           6/23/2023                7/5/2023                B1

3                           6/23/2023                7/8/2023                A1

Using the count of distinct Shipped On Dates:

Order 1 had 2 shipments

Order 2 had 1 shipment

Order 3 had 3 shipments

The average shipments per order for orders placed on 6/23/2023 is (2 + 1 + 3) / 3, or 2 shipments per order.

This is the DAX I'm using now, which presents the right answer but is extremely slow.

Avg Shipment Count =

var __table = summarize('MyTable', 'MyTable'[Order Number], "NumShipments", DISTINCTCOUNT('MyTable'[Shipped on]))

return AVERAGEX(__table, [NumShipments])

Is there an alternate syntax that will give me the proper results but has a good chance of running faster?  I have over 135K rows in the dataset (and this will grow thru year end).

Thank you very much for your help!

Rich

[Anonymous · Accepted solution]

Hi @RichardJChester ,

Please try:

Avg Shipment Count2 = 
VAR OrderDates =
    VALUES ( 'Table'[Ordered On Date] )
VAR Orders =
    SUMMARIZE ( 'Table', 'Table'[Order Number], 'Table'[Ordered On Date] )
VAR ShipmentsPerOrder =
    ADDCOLUMNS (
        Orders,
        "Shipments",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Shipped On Date] ),
                ALLEXCEPT ( 'Table', 'Table'[Order Number], 'Table'[Ordered On Date] )
            )
    )
VAR TotalShipments =
    SUMX ( ShipmentsPerOrder, [Shipments] )
VAR TotalOrders =
    COUNTROWS ( Orders )
RETURN
    DIVIDE ( TotalShipments, TotalOrders )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[RichardJChester]

Thank you very much!  This helped a tremendous amount!

Rich