Forum Discussion
Daily Average as a Column in Matrix
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 🙂
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.