cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
boriz Frequent Visitor
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):

lowest granularity.PNG

 

This is how it should look like one level higher - no more minutes:

hourly granularity.PNG

 

This is how it should look like if we remove Time dimension:

daily granularity.PNG

 

And if I remove the Date dimension:

customer granularity.PNG

 

And If I don't use any dimensions:

highest granularity.PNG


Can anyone help me?How to achieve this in DAX?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
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] )
)

1.png

 

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
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
v-cherch-msft Super Contributor
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] )
)

1.png

 

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
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 6 members 1,065 guests
Please welcome our newest community members: