Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
28 | |
23 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |