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

## Aggregate daily measure to weekly

This has to be easy enough to do, but I'm drawing a blank.  I have a created measre that calculates the average number of units per day.  I need to to display the sum of those daily averages at the week level.  In the following example, I need to show 7.9 at at week level (I do NOT want to show the average value of 1.58):

 Day Avg Mon 1.2 Tue 2.0 Wed 1.5 Thur 1.0 Fri 2.2

This data will be charted showing multiple weeks for trending purposes.

Any help is appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: Aggregate daily measure to weekly

HI @SGT,

You can try follow below steps to get the summaried average value:

1. Add calculated column 'Day of Week' to store the weekday.

```Day of Week =
FORMAT ( [Date], "dddd" )
```

2. Write a measure with conditional formula to replace total level show the summary result.

```Dynamic Result =
IF (
DISTINCTCOUNT ( 'Table'[Day of Week] ) = 7,
SUMX ( SUMMARIZE('Table',[Date].[Year],[Date].[Month],[Day of Week],"AVG",AVERAGE([Amount])), [AVG]),
AVERAGE ( [Amount] )
)```

3. Create a matrix visual with above measure. If above not help, can you please share some sample data and the measure formula to test?

Regards,
Xiaoxin Sheng

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

For learning resources/Release notes, please visit: | |
1 REPLY 1 Community Support Team

## Re: Aggregate daily measure to weekly

HI @SGT,

You can try follow below steps to get the summaried average value:

1. Add calculated column 'Day of Week' to store the weekday.

```Day of Week =
FORMAT ( [Date], "dddd" )
```

2. Write a measure with conditional formula to replace total level show the summary result.

```Dynamic Result =
IF (
DISTINCTCOUNT ( 'Table'[Day of Week] ) = 7,
SUMX ( SUMMARIZE('Table',[Date].[Year],[Date].[Month],[Day of Week],"AVG",AVERAGE([Amount])), [AVG]),
AVERAGE ( [Amount] )
)```

3. Create a matrix visual with above measure. If above not help, can you please share some sample data and the measure formula to test?

Regards,
Xiaoxin Sheng

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

For learning resources/Release notes, please visit: | |