Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
65 | |
62 | |
55 |
User | Count |
---|---|
171 | |
109 | |
105 | |
73 | |
71 |