Forum Discussion
Averages per Weekday
Hi all,
Given the following table, my main goal is to have a matrix that has weekdays as columns and average sales as values. I want to calculate the average total sales value per weekday in two ways:
- Only considering weekdays with sales.
- Considering all weekdays, including those with no sales.
| Sale Value | Sales Date |
| 200 | 03.01.2022 |
| 200 | 17.01.2022 |
| 200 | 31.01.2022 |
| 200 | 14.02.2022 |
| 200 | 28.02.2022 |
Note that all those dates are Mondays, with sales every other Monday valued at 200.
Expected Measures:
- Measure A that returns 200 (average sales on those Mondays that have sales).
- Measure B that returns around 100 (average sales across all Mondays, including those with no sales). The exact amount would depend on the time frame used for the calculation, while the first measure should not depend on that as long as there is at least one of the sales in the used time frame.
There are really similar problems to find here, some even marked as solved, yet those solutions did not work for me at all. Getting a measure that returns the 200 is not a problem. Actually you don't even need an actual measure for that because you can just use Sales for values in the matrix and change that to Average of Sales in the matrix options. But I can't seem to find a measure that actually returns the 100. This for example only returns the total, not the average:
AverageSalesAllWeekdays =
CALCULATE(
AVERAGEX(
VALUES(Calendar[Weekday]),
VAR CurrentWeekday = Calendar[Weekday]
RETURN
CALCULATE(
SUM(Sales[Sales]),
FILTER(
Sales,
FORMAT(Sales[Date], "dddd") = CurrentWeekday
)
)
)
)
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 )
7 Replies
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.
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 )
-
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 )
Hi x7 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
