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 need to create a bar chart with average Sales by Product based on the filter I choose by each day of the week.
For example.. if I select Monday in the Dates tables filter, the chart should show the averages of app counts submitted on only Mondays of that month/year/ time period selected in time slicer and similarly for other days of the week.
I would really appreciate if someone can help.
Solved! Go to Solution.
Hi @Anonymous,
Based on my test, you can refer to below steps:
1.I have entered some sample data to test for your problem. Now you can create a calculated column to calculate the weekday.
weekday = WEEKDAY([Date],1)
2.Create a Clustered column chart value and add the [Year], [Month], [Day], and [Value] fields and modify the [Value] field from "Sum" to "Average".
3.Create a Slicer visual and add the [weekday] field.
Now you can filter your data manually.
You can also download the PBIX file to have a view. If it doesn’t meet your requirements, please provide some dummy data and clarify the requirement.
Regards,
Daniel He
Hi @Anonymous,
Based on my previous test pbix file, you can refer to below steps:
1.Create a new measure to calculate the moving average of last 30 days.
Apps 30D Moving Average = CALCULATE(AVERAGE(Table1[Value]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-30,DAY))
2.Create a Clustered column chart and add the [Date] and [Apps 30D Moving Average] field.
3.Use the “weekday slicer” , you can filter last 30 Mondays/Tuesdays data.
You can also download the PBIX file to have a view.
Regards,
Daniel He
Hi @Anonymous,
Based on my test, you can refer to below steps:
1.I have entered some sample data to test for your problem. Now you can create a calculated column to calculate the weekday.
weekday = WEEKDAY([Date],1)
2.Create a Clustered column chart value and add the [Year], [Month], [Day], and [Value] fields and modify the [Value] field from "Sum" to "Average".
3.Create a Slicer visual and add the [weekday] field.
Now you can filter your data manually.
You can also download the PBIX file to have a view. If it doesn’t meet your requirements, please provide some dummy data and clarify the requirement.
Regards,
Daniel He
@v-danhe-msft Thank you for sharing the solution. This was really helpful.
Now with this bar chart, I also need to include another bar of Moving Average of last 'n' number of Mondays/Tuesdays based on the Weekday selected in the filter. Basically, I have to compare the Avg number of Sales with the Moving Average of Sales by that day for a time frame.
Below is the DAX formula I use to calculate the Moving Average of last 30 days:
Apps 30D Moving Average= AVERAGEX(
DATESINPERIOD(
Dates[DateKey],
LASTDATE(Dates[DateKey]),
-30,DAY),
[Apps])
But, how to implement it for last 30 Mondays/Tuesdays etc. based on the Weekday slicers selected ? I would really appreciate if we could resolve this.
Hi @Anonymous,
Based on my previous test pbix file, you can refer to below steps:
1.Create a new measure to calculate the moving average of last 30 days.
Apps 30D Moving Average = CALCULATE(AVERAGE(Table1[Value]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-30,DAY))
2.Create a Clustered column chart and add the [Date] and [Apps 30D Moving Average] field.
3.Use the “weekday slicer” , you can filter last 30 Mondays/Tuesdays data.
You can also download the PBIX file to have a view.
Regards,
Daniel He
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 |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |