Group then Distinct Count then Average using DAX

Question

I have a need to analyze the Average Number of Shipments per Order per Day on an imported table as a DAX measure.&nbsp; 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.&nbsp; I have a solution that works, but it is show to present data in my visuals.&nbsp; I wonder if there's a more efficient way to do this - any ideas will be most appreciated!&nbsp;The final analysis shows the average number of shipments per order by order date.&nbsp;&nbsp;&nbsp;For example, I might have 3 orders placed on June 23.&nbsp; &nbsp;Here is a simplified view of my data.&nbsp;Order Number&nbsp; &nbsp; &nbsp;Ordered On Date&nbsp; &nbsp; &nbsp;Shipped On Date&nbsp; &nbsp;Branch1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/23/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6/25/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/23/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6/25/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/23/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7/1/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/23/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7/1/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/23/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7/8/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/23/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7/8/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B23&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/23/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7/1/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/23/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7/5/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/23/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7/5/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B13&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/23/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7/8/2023&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A1&nbsp;Using the count of distinct Shipped On Dates:Order 1 had 2 shipmentsOrder 2 had 1 shipmentOrder 3 had 3 shipments&nbsp;The average shipments per order for orders placed on 6/23/2023 is (2 + 1 + 3) / 3, or 2 shipments per order.&nbsp;This is the DAX I'm using now, which presents the right answer but is extremely slow.&nbsp;Avg Shipment Count =var __table = summarize('MyTable', 'MyTable'[Order Number], "NumShipments", DISTINCTCOUNT('MyTable'[Shipped on]))return AVERAGEX(__table, [NumShipments])&nbsp;Is there an alternate syntax that will give me the proper results but has a good chance of running faster?&nbsp; I have over 135K rows in the dataset (and this will grow thru year end).&nbsp;Thank you very much for your help!&nbsp;Rich&nbsp;

anonymous · Accepted Answer

Hi&nbsp;RichardJChester&nbsp;,
&nbsp;
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,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;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.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum

Forum Discussion

Group then Distinct Count then Average using DAX

2 Replies

Related content

Average - Distinct Count

Count of groups of distinct count by group

Grouped Average

DISTINCT COUNT AND GROUP BY

average by group