Forum Discussion
Two-level aggregation using DAX measures
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
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 :)
6 Replies
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 :)
Thanks!
This is exactly what I was looking for :)
This is a good example of aggregating across multiple tables
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!
Hi,
I think...... the visuals aggregation functions will take care of this.
create a standard averge mearsure on the quantity.
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
