Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
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
User | Count |
---|---|
98 | |
90 | |
78 | |
72 | |
65 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |