Forum Discussion
Matrix table with custom row labels and multiple measures.
Hi Community,
I have a requirement to build the below report called 'Final Report Matrix' using a matrix table or whichever the visualization best suits to make this report. I've attached the spreadsheet with the business logic as comments/notes for each cell for your reference.
I have tried to create measures but could not land up to the table above, the challenge being able to create the row labels on the left!
I have also attached the data sheet with test/sample data so you can use the data while testing.
Note: I'm using a Calculated Table (Documents) for all my reports because I had to cleanse data as seen in the data model screenshot.
Below is the data model screenshot for your reference, if that helps:
Can anyone please help/guide me how to do this report? Your time is greatly appreciated.
Attached are the spreadsheets of both Final Report Matrix and data sheet.
Thank you so much in advance, C
8 Replies
- amitchandak
Super User
Anonymous , if you are looking for this week vs last week, Have a date table with folliwng week columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 // Monday //Sunday 'Date'[Date]+-1*WEEKDAY('Date'[Date])+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2) // Sunday // Saturday // 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date])
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))Hi Amit,
Thanks for your quick reply, greatly appreciated.
Can you please help me with how to do the first column 'Vendor Data' with all different labels below such as 'Total Drawings', 'Drawings Incomplete (Requiring Action)', 'Drawings Complete (No action required)', 'Total Vendor Data', 'Vendor Data Incomplete (Requiring Action)', 'Vendor Data Complete (No action required)', and the columns C, D, E, F, G, H?
Any takers.. please...?
Thanks in advance, C
- WinterMist
Impactful Individual
Was there ever an answer on how to do this?
I have a similar requirement and have not yet found a way to do this.
Regards,
Nathan
