Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi @AaaaD1 ,
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
Hi @AaaaD1 ,
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
@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
@AaaaD1 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]
)
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |