Forum Discussion
Averages per Weekday
To report on things that are not there you need to use disconnected tables and/or crossjoins
Add a disconnected calendar table to your data model and then use it in the visual. Your measure can then do the rest.
This solution seems to work with the sample data and an unconnected calendar:
AverageSalesIncludingNoSales = VAR CurrentWeekday = SELECTEDVALUE(UnconnectedCalendar[Weekday]) // Get the current weekday from the visual context VAR TotalSales = CALCULATE( SUM(Sales[Sales]), FILTER(Sales, WEEKDAY(Sales[Date], 2) = CurrentWeekday) // Filter sales for the current weekday ) VAR TotalWeekdays = COUNTROWS( FILTER( UnconnectedCalendar, UnconnectedCalendar[Weekday] = CurrentWeekday // Count all instances of the current weekday ) ) RETURN IF( TotalWeekdays = 0, 0, DIVIDE(TotalSales, TotalWeekdays, 0) // Use 0 as an alternate result for division by zero )
Hi x7 ,
Thank you for reaching out to the Microsoft Fabric Community.
FYI:
mentioned by lbendlin , try to use disconnected calendar table.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
Thank you for your reply and sorry for my late response.
First, I think I failed to communicate what my goal is, which is the below table, just with AverageSalesOverAllDays being 100 (assuming equal amounts of mondays that have sales and that have no sales) for Monday and zero on all other days and average of Sales being 200 for Monday and zero for all other days.
So the measure should add up the amount of all sales of all let's say mondays and then divide by the number of mondays in a chosen time frame for Measure B (AverageSalesOverAllDays). For Measure A (Average of Sales) it should add up the amount of all sales of all mondays and then divide by the number of mondays that have any sales at all in a chosen time frame.
The table above was created using your version of Measure B, which results in adding up the sales instead of averaging them. Regarding your measure I have some questions. Fist, what is the purpose of the variable DaysWithSales, it is declared but never used. Then you divide by COUNTROWS(AllWeekdays), so by the total amount of days in the calendar table. Shouldnt this only count those rows/days of the calendar table that are of the same weekday as the respective sales date? Or is this automatically done through the PowerBI context magic? Considering two weeks where on Monday has sales of 200 and the other none, I want to divide 200 by the amount of Mondays in that period, i.e. 2, not 200 by the total number of days in that period, i.e. 14.
This solution seems to work with the sample data and an unconnected calendar:
AverageSalesIncludingNoSales = VAR CurrentWeekday = SELECTEDVALUE(UnconnectedCalendar[Weekday]) // Get the current weekday from the visual context VAR TotalSales = CALCULATE( SUM(Sales[Sales]), FILTER(Sales, WEEKDAY(Sales[Date], 2) = CurrentWeekday) // Filter sales for the current weekday ) VAR TotalWeekdays = COUNTROWS( FILTER( UnconnectedCalendar, UnconnectedCalendar[Weekday] = CurrentWeekday // Count all instances of the current weekday ) ) RETURN IF( TotalWeekdays = 0, 0, DIVIDE(TotalSales, TotalWeekdays, 0) // Use 0 as an alternate result for division by zero )