Forum Discussion

Regular Visitor

3 years ago

Solved

Group then Distinct Count then Average using DAX

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 slic...

Anonymous

3 years ago

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

Anonymous

Not applicable

3 years ago

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