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

DayAvg
Mon1.2
Tue2.0
Wed1.5
Thur1.0
Fri2.2

 

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

 

Any help is appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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.

1.PNG

 

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

1.PNG

 

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: | |