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
You can try this approach while using the weekday logic:
1) Create a separate (disconnected) calendar table that contains just the list of weekdays:
{"Monday", "Tuesday", "Wednesday", ...}
2) Create a measure that calculates the average per weekday by dividing the total value for that weekday by the count of distinct dates in your actual fact table that fall on that weekday.
AveragePerWeekday =
VAR CurrentDay = SELECTEDVALUE('WeekdayTable'[Weekday])
VAR TotalValue =
CALCULATE(
SUM('Sales'[Amount]),
WEEKDAY('Sales'[Date], 2) =
SWITCH(CurrentDay,
"Monday", 1,
"Tuesday", 2,
"Wednesday", 3,
"Thursday", 4,
"Friday", 5,
"Saturday", 6,
"Sunday", 7
)
)
VAR CountDays =
CALCULATE(
DISTINCTCOUNT('Sales'[Date]),
WEEKDAY('Sales'[Date], 2) =
SWITCH(CurrentDay,
"Monday", 1,
"Tuesday", 2,
"Wednesday", 3,
"Thursday", 4,
"Friday", 5,
"Saturday", 6,
"Sunday", 7
)
)
RETURN DIVIDE(TotalValue, CountDays)
It helps me because:
-
By disconnecting the weekday from your main date table, you avoid issues with sparsity and uneven distribution.
-
The logic above dynamically filters the fact table for each weekday and averages it only across the number of actual days present for that weekday in your data.
I tried this approach without succes, my problem might have been that my calendar was connected to Sales table through the date field. I thought this was the whole point of modeling relationships in Power BI, but as pointed out by others in that case it seems to be the wrong approach. I'd be interested in your measure though. This is the solution "I" (read: AI) came up with:
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
)