Two-level aggregation using DAX measures

Hi RafalK

You can use nested AVERAGEX functions to get an average of averages.

I'm assuming that if you happen to have multiple rows per Product per Day, you want the Quantity to be summed at a Product/Day level.

(If you can guarantee you will never have multiple rows per Product per Day, you could simplify the below a bit).

Either of these should work:

Average of Averages v1 = 
AVERAGEX (
    VALUES ( YourTable[Product] ),
    AVERAGEX (
        VALUES ( YourTable[Day] ),
        CALCULATE ( SUM ( YourTable[Quantity] ) )
    )
)

Average of Averages v2 = 
AVERAGEX (
    VALUES ( YourTable[Product] ),
    CALCULATE (
        AVERAGEX (
            VALUES ( YourTable[Day] ),
            CALCULATE ( SUM ( YourTable[Quantity] ) )
        )
    )
)

The second measure avoids redundant iteration over values of the Day column for each Product, with the additional CALCULATE. This could perform better if each Product sells on different sets of Days.

Regards,

Owen :)