Forum Discussion
Solved
Calculating Averages for the same data for different time buckets (Daily/Weekly/Monthly)
Hi I have sales data at invoice date level. I want to have average calculations at 3 time bucket levels on the same data a) Daily sales average: Total of the daily sales and divide by total numb...
Hi Xiaoxin Sheng
Thank you very much. The result is exactly what is needed. I guess, we can get daily average on the same lines. Let me try and come back to you.
Appreciate your help.
Regards
Prasad
HI Prasad1,
You can check below formula to use formula to get average of current group.
Measures:
Current Count =
SWITCH (
TRUE (),
ISFILTERED ( 'Table'[Quarter] ) && ISFILTERED ( 'Table'[Month] ) && ISFILTERED ( 'Table'[Week] ), COUNTROWS ( SUMMARIZE (ALLSELECTED('Table'), [Quarter], [Month], [Week] ) ),
ISFILTERED ( 'Table'[Quarter] ) && ISFILTERED ( 'Table'[Month] ), COUNTROWS ( SUMMARIZE (ALLSELECTED('Table'), [Quarter], [Month] ) ),
ISFILTERED ( 'Table'[Quarter] ) && ISFILTERED ( 'Table'[Week] ), COUNTROWS ( SUMMARIZE ( ALLSELECTED('Table'), [Quarter], [Week] ) ),
ISFILTERED ( 'Table'[Month] ) && ISFILTERED ( 'Table'[Week] ), COUNTROWS ( SUMMARIZE ( ALLSELECTED('Table'), [Month], [Week] ) ),
ISFILTERED ( 'Table'[Quarter] ), COUNTROWS ( ALLSELECTED ( 'Table'[Quarter] ) ),
ISFILTERED ( 'Table'[Month] ), COUNTROWS ( ALLSELECTED ( 'Table'[Month] ) ),
ISFILTERED ( 'Table'[Week] ), COUNTROWS ( ALLSELECTED ( 'Table'[Week] ) ),
COUNTROWS(ALLSELECTED('Table'))
)
Dynamic AVG =
VAR GroupCount =
SWITCH (
TRUE (),
ISFILTERED ( 'Table'[Quarter] ) && ISFILTERED ( 'Table'[Month] ) && ISFILTERED ( 'Table'[Week] ), COUNTROWS ( SUMMARIZE (ALLSELECTED('Table'), [Quarter], [Month], [Week] ) ),
ISFILTERED ( 'Table'[Quarter] ) && ISFILTERED ( 'Table'[Month] ), COUNTROWS ( SUMMARIZE (ALLSELECTED('Table'), [Quarter], [Month] ) ),
ISFILTERED ( 'Table'[Quarter] ) && ISFILTERED ( 'Table'[Week] ), COUNTROWS ( SUMMARIZE ( ALLSELECTED('Table'), [Quarter], [Week] ) ),
ISFILTERED ( 'Table'[Month] ) && ISFILTERED ( 'Table'[Week] ), COUNTROWS ( SUMMARIZE ( ALLSELECTED('Table'), [Month], [Week] ) ),
ISFILTERED ( 'Table'[Quarter] ), COUNTROWS ( ALLSELECTED ( 'Table'[Quarter] ) ),
ISFILTERED ( 'Table'[Month] ), COUNTROWS ( ALLSELECTED ( 'Table'[Month] ) ),
ISFILTERED ( 'Table'[Week] ), COUNTROWS ( ALLSELECTED ( 'Table'[Week] ) ),
COUNTROWS(ALLSELECTED('Table'))
)
RETURN
DIVIDE ( SUMX ( ALLSELECTED( 'Table' ), [Amount] ), GroupCount, 0 )
Result:
Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng
Thank you very much. The result is exactly what is needed. I guess, we can get daily average on the same lines. Let me try and come back to you.
Appreciate your help.
Regards
Prasad