Two-level aggregation using DAX measures

[RafalK]

Hi guys,

I need to calculate an average quantity per product and then per total, based on the product average.

I can make it work in DAX just as long as I only aggregate the data once. I just can't figure out how to aggregate to one level first and then aggregate that again.

I have this table as an input:

Product	Day	Quantity
P1	Day 1	10
P1	Day 2	12
P1	Day 3	20
P2	Day 1	5
P2	Day 2	7
P2	Day 3	12
P3	Day 1	23
P3	Day 2	30
P3	Day 3	35

Now I need to first calculate "Avg Quantity":

Product	Avg Quantity
P1	14,00
P2	8,00
P3	29,33

And then, based on that the "Avg of Averages"

Avg of Average

17,11

My first aproach was to simply create new tables with aggregates and this does work, however I loose the option to filter the data by Day, using a slicer.

For those with SQL backgroupd. I need to do this:

select
AVG(AvgQuantity) "AvgofAverages"
from (
   select
     Product,

     AVG(Quantity) "AvgQuantity" 
   where Day = <selection> 
   group by Product
) X

Regards

[afzalphatan]

Jus check... If P1 is not sold in DAY 1 .. then there are 2 ways to enter in ur data table...

1. To mention value of Quantity as Zero in Day1 

2. There is no transaciton in data table of P1 and Day1 ( As no quantity was sold... there wouldn't be any transaction )

Whats ur pattern.. check if the solution works

[OwenAuger · Accepted solution]

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 🙂

[Pocho]

Hi Owen,

I have a similar case were I have 12 months of data and need to have the average of the first 6 months and the second 6 months to compare changes period vs period. Any ideas?

thanks!

[Joerobert]

This is a good example of aggregating across multiple tables

[RafalK]

Thanks! 

This is exactly what I was looking for 🙂

[Dog]

Hi,

I think...... the visuals aggregation functions will take care of this. 

create a standard averge mearsure on the quantity.