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