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