Forum Discussion
Multi-level aggregation using DAX
Hello,
I've encountered a problem when writing a DAX measure.
My design:
- A fact table
- A Date dimension (granularity: day)
- A Time dimension (it has Hour integers and Minute integers - only for quarters (0, 15, 30, 45)
What I want to achieve:
- Force Tabular to always AVERAGE my measure from 15-minute intervals to an hour.
- Force Tabular to always SUM the averages from first bullet.
- This has to work even when Time/Date dimensions aren't used in the pivot table/PBI.
Let's suppose this is how data at the lowest granularity looks like (there are more dimensions, though):
This is how it should look like one level higher - no more minutes:
This is how it should look like if we remove Time dimension:
And if I remove the Date dimension:
And If I don't use any dimensions:
Can anyone help me?How to achieve this in DAX?
Thanks in advance.
Hi Anonymous
You may try below measure:
Test = SUMX ( SUMMARIZE ( Table1, Table1[Dim Customer], Table1[Dim Date], Table1[Dim Time-Hour] ), SUM ( Table1[Value] ) / COUNT ( Table1[Dim Time-Hour] ) )Regards,
Cherie
2 Replies
- v-cherch-msftMicrosoft Employee
Hi Anonymous
You may try below measure:
Test = SUMX ( SUMMARIZE ( Table1, Table1[Dim Customer], Table1[Dim Date], Table1[Dim Time-Hour] ), SUM ( Table1[Value] ) / COUNT ( Table1[Dim Time-Hour] ) )Regards,
Cherie
- GilbertQSuper UserHi there
What measures have you tried to do to get this working?