Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |