Daily Average as a Column in Matrix

[BODMON]

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

[FreemanZ · Accepted solution]

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. 

[FreemanZ · Accepted solution]

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. 

[BODMON]

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!

[FreemanZ]

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. 

[BODMON · Accepted solution]

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 🙂

[Greg_Deckler]

@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
    __Result

Also, you'll likely want this: The New Hotness (Custom Matrix Hierarchy) - Microsoft Power BI Community

[BODMON]

@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.