Forum Discussion
Daily Average as a Column in Matrix
Hello community!
I'm looking to create a table and add a daily average weekly column for the currently selected week, and a daily average weekly column for the previous week as a comparison.
Example of the Output table I'm looking for:
I'm able to do everything highlighted in green no problem, but my challenge with this is adding that average weekly in the matrix table and having it displayed as a column rather than a row.
I have a date table that I created that has every day in the year and a value of 0 if it's a holiday and 1 if it's a business day.
So in the past I've used it to just sum the days between the two periods and then divide the total Tickets by the total number of business days in a period. This is to get the Daily Average so we can compare.
Below are 2 sample data sets. The first has the Users and how many Tickets they've closed, and the other data is just the date table indicating if it's a business day or not. Any help would be appreciated! Thanks.
Sample_User data
| User | Ticket_date | Tickets |
| User1 | 1/1/2023 | 0 |
| User1 | 1/2/2023 | 0 |
| User1 | 1/3/2023 | 10 |
| User1 | 1/4/2023 | 15 |
| User1 | 1/5/2023 | 20 |
| User1 | 1/6/2023 | 25 |
| User1 | 1/9/2023 | 10 |
| User1 | 1/10/2023 | 15 |
| User1 | 1/11/2023 | 20 |
| User1 | 1/12/2023 | 25 |
| User1 | 1/13/2023 | 30 |
| User1 | 1/16/2023 | 12 |
| User1 | 1/17/2023 | 19 |
| User1 | 1/18/2023 | 25 |
| User1 | 1/19/2023 | 22 |
| User1 | 1/20/2023 | 29 |
| User1 | 1/23/2023 | 24 |
| User1 | 1/24/2023 | 26 |
| User1 | 1/25/2023 | 20 |
| User1 | 1/26/2023 | 10 |
| User1 | 1/27/2023 | 15 |
| User2 | 1/2/2023 | 0 |
| User2 | 1/3/2023 | 15 |
| User2 | 1/4/2023 | 20 |
| User2 | 1/5/2023 | 25 |
| User2 | 1/6/2023 | 30 |
| User2 | 1/9/2023 | 15 |
| User2 | 1/10/2023 | 20 |
| User2 | 1/11/2023 | 25 |
| User2 | 1/12/2023 | 30 |
| User2 | 1/13/2023 | 35 |
| User2 | 1/17/2023 | 18 |
| User2 | 1/18/2023 | 17 |
| User2 | 1/19/2023 | 25 |
| User2 | 1/20/2023 | 40 |
| User2 | 1/23/2023 | 33 |
| User2 | 1/24/2023 | 35 |
| User2 | 1/25/2023 | 10 |
| User2 | 1/26/2023 | 19 |
| User2 | 1/27/2023 | 15 |
| User3 | 1/2/2023 | 0 |
| User3 | 1/3/2023 | 20 |
| User3 | 1/4/2023 | 25 |
| User3 | 1/5/2023 | 30 |
| User3 | 1/6/2023 | 35 |
| User3 | 1/9/2023 | 20 |
| User3 | 1/10/2023 | 25 |
| User3 | 1/11/2023 | 30 |
| User3 | 1/12/2023 | 35 |
| User3 | 1/13/2023 | 40 |
| User3 | 1/17/2023 | 17 |
| User3 | 1/18/2023 | 30 |
| User3 | 1/19/2023 | 33 |
| User3 | 1/20/2023 | 20 |
| User3 | 1/23/2023 | 24 |
| User3 | 1/24/2023 | 40 |
| User3 | 1/25/2023 | 34 |
| User3 | 1/26/2023 | 10 |
| User3 | 1/27/2023 | 10 |
Sample_Date table
| Date | Day | Business_Day | WK_Days | Week |
| 01/01/2023 | Sunday | 0 | 4 | 1 |
| 01/02/2023 | Monday | 0 | 4 | 1 |
| 01/03/2023 | Tuesday | 1 | 4 | 1 |
| 01/04/2023 | Wednesday | 1 | 4 | 1 |
| 01/05/2023 | Thursday | 1 | 4 | 1 |
| 01/06/2023 | Friday | 1 | 4 | 1 |
| 01/07/2023 | Saturday | 0 | 4 | 1 |
| 01/08/2023 | Sunday | 0 | 5 | 1 |
| 01/09/2023 | Monday | 1 | 5 | 2 |
| 01/10/2023 | Tuesday | 1 | 5 | 2 |
| 01/11/2023 | Wednesday | 1 | 5 | 2 |
| 01/12/2023 | Thursday | 1 | 5 | 2 |
| 01/13/2023 | Friday | 1 | 5 | 2 |
| 01/14/2023 | Saturday | 0 | 5 | 2 |
| 01/15/2023 | Sunday | 0 | 5 | 2 |
| 01/16/2023 | Monday | 1 | 5 | 3 |
| 01/17/2023 | Tuesday | 1 | 5 | 3 |
| 01/18/2023 | Wednesday | 1 | 5 | 3 |
| 01/19/2023 | Thursday | 1 | 5 | 3 |
| 01/20/2023 | Friday | 1 | 5 | 3 |
| 01/21/2023 | Saturday | 0 | 5 | 3 |
| 01/22/2023 | Sunday | 0 | 5 | 3 |
| 01/23/2023 | Monday | 1 | 5 | 4 |
| 01/24/2023 | Tuesday | 1 | 5 | 4 |
| 01/25/2023 | Wednesday | 1 | 5 | 4 |
| 01/26/2023 | Thursday | 1 | 5 | 4 |
| 01/27/2023 | Friday | 1 | 5 | 4 |
| 01/28/2023 | Saturday | 0 | 5 | 4 |
| 01/29/2023 | Sunday | 0 | 5 | 4 |
hi BODMON
not sure if i fully get you, adding additional column to a matrix might not be easy, you may try to add new table or matrix visual, like this:
the code for the two measures:
Daily Avg CW = VAR _workingdays = COUNTROWS( FILTER( Dates, Dates[Business_Day]=1 ) ) RETURN DIVIDE( SUM(TableName[Tickets]), _workingdays ) Daily Avg LW = CALCULATE( [Daily Avg CW], DATEADD(Dates[Date], -7, DAY) )in the second table visual, there are actually three fields: user column and the two measures.
Crazy. Figured it out.
The original source data for the ticket table has a date with a time stamp on it.
So even though its format was date and I convert it to short date in PowerBI, it still has an impact. It was not liking the relationship between the ticket table and the date table.
I had to go to the source in SQL and convert it there so it would come through just as a date.
Very annoying when something so small takes so long to troubleshoot.
Thank you for your help FreemanZ , the weekly averages work!!
Now I need to see how I can put those weekly average columns into one matrix table, rather than 2 matrix tables…reading Greg_Deckler
post and it seems like it’s going to be fun 🙂
6 Replies
BODMON So like this?
Daily Average = VAR __Days = ( MAX('Dates'[Date]) - MIN('Dates'[Date]) ) * 1. VAR __Tickets = SUM('Table'[Tickets]) VAR __Result = DIVIDE(__Tickets, __Days, 0) RETURN __ResultAlso, you'll likely want this: The New Hotness (Custom Matrix Hierarchy) - Microsoft Power BI Community
Greg_Deckler Thank you for your reply! I read your post before I posted on How To Get Your Question Answered Quickly so hopefully I did an OK job 🙂
I created a Column:
WeeklyBusinessDays =
VAR VarDate = DATE(2023,01,16)
VAR MaxDate = DATE(2023,01,20)
RETURN
if ( Dates[Date] <= MaxDate,
CALCULATE (
SUM(Dates[Business_Day]),
FILTER (
Dates,
Dates[Date] >= VarDate && Dates[Date] <= MaxDate)
)
, 0)So this would return 5 days which is correct. Problem is it's not dynamic because all I did was enter in a fixed date for both Variables. I'd need it to calculate how many business days between the first day of the week (Monday) and the last day of the week (Friday).
In an ideal world it wouldn't even just be limited to only a week, but more of a filter that could adjust the date range and calculate the amount of busines days between the start and end date selected. But i'd settle for it always being a week. Any thoughts?
Then I created a measure:
DailyAverage = SUM('Table'[Tickets])/MAX(Dates[WeeklyBusinessDays])
This gives me the correct result. However to throw that in a matrix table as a value seems to make the Matrix goes crazy, as you pointed out in your "The New Hotness" post.
hi BODMON
not sure if i fully get you, adding additional column to a matrix might not be easy, you may try to add new table or matrix visual, like this:
the code for the two measures:
Daily Avg CW = VAR _workingdays = COUNTROWS( FILTER( Dates, Dates[Business_Day]=1 ) ) RETURN DIVIDE( SUM(TableName[Tickets]), _workingdays ) Daily Avg LW = CALCULATE( [Daily Avg CW], DATEADD(Dates[Date], -7, DAY) )in the second table visual, there are actually three fields: user column and the two measures.
Hi FreemanZ !
Thanks for your reply!
I tried your method and it seems to fix part of it. It seems like the measures you suggested only work with the date in the dates table.
My issue is now with the dates. Which date did you use for the date slicer? The one from the ticket table or the dates table?
Did you create a relationship between them? I tried to and it didn't work for some reason so I deleted the relationship.
I would need one date filter to be able to control all.
Here's a link to the pbix file: Sample PowerBI.pbix
Thanks!
hi BODMON
the slicer is fed by the dates table. the dates table is linked with the ticket table.
as [Daily Avg LW] refers to 7 days before the sliced range, so try either delete the 2nd slicer or extend to cover the last week.
As a general rule, try always use the field from the dimensional table, like dates in this case.
