Average of GroupBy and sum

[Anonymous]

i have a table with date, status and values(with other column but these are the main ones), its daily data and i would like to have a chart that shows weekly average.
How can i summarize teh data on weekly level. i wnat to group the data based on dates and status and then show the weekly average.
can someone please help me with it.

thanks

[saurabhtd]

@Anonymous You need to create a calculated column to extract the week of the year from date column present in the table. You can use the following formula:

Week = WEEKNUM(Date, 1)

here I have taken "1" since I am assuming Sunday as the first day of the week. Visit this link for more info on WEEKNUM WEEKNUM – DAX Guide 

DAX formula to calculate weekly average of values column group by week and status

Weekly Average =
VAR _week = SELECTEDVALUE('Table'[Week])
VAR _status = SELECTEDVALUE('Table'[Status])
RETURN
AVERAGEX(
FILTER(
'Table',
'Table'[Week] = _week
&& 'Table'[Status] = _status
),
[Values]
)

[Anonymous]

@saurabhtd  thanks for reaponding

actually i need it in a stack bar chart where weeks are on x-axis and status is legend 
i created a endofWeek column and using it in x-axis then 
var _sum=sum(values)
return
averagex(selectcolumn(table,"date",table[Date],"status",table[status]),_sum)

this still doesn't give correct result, where am i going wrong

[Anonymous · Accepted solution]

Hi @Anonymous ,

If I understand correctly, the issue is that you want to group the data based on dates. Please try the following methods and check if they can solve your problem:

1.Create the simple table.

2.Create the new column.

weeknum = WEEKNUM('Table 2'[Date], 2)

3.Create the new measure to calculate.

Measure = CALCULATE(AVERAGE('Table 2'[values]), ALLEXCEPT('Table 2', 'Table 2'[status]), 'Table 2'[weeknum] = MAX('Table 2'[weeknum]))

4.Select the column chart visual and drag the field to the columns.

5.The result is shown below.

Best Regards,

Wisdom Wu