Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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-msft's avatar
    v-cherch-msft
    Microsoft 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

  • Hi there

    What measures have you tried to do to get this working?