cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Multi-level aggregation using DAX

Hi @boriz

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super User

## Re: Multi-level aggregation using DAX

Hi there

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

Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"

Power BI Blog
Super Contributor

## Re: Multi-level aggregation using DAX

Hi @boriz

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

## Helpful resources

Announcements

#### How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 248 members 2,616 guests
Recent signins:
Please welcome our newest community members: