Forum Discussion
Help with Matrix
Anonymous
From your desciption, you should:
1) unpivot both "Planned Vacation" and "Actual days taken" tables to have the months and Year in a single column each.
2) Create lookup tables with unique values for:
a) months (include an index column for sorting purposes): join with a one-to.many relationship to both your "Planned Vacation" and "Actual days taken tables"
b) years: join with a one-to.many relationship to both your "Planned Vacation" and "Actual days taken tables"
c) Employee ID: join with a one-to.many relationship to all your tables
3) create measures to calculate the number of [planned days], [number of days taken] and [vacation days balance]
4) create a matrix visual using:
a) Employee Id field from your lookup table as rows
b) the month and year fields from your lookup tables as columns (or you can use the year field as a filter in the filter pane if you want to restrict the data to a specific year.)
c) include your measures as values
5) go into the formatting pane for the matrix and under "Values" select "show on rows".
Does that work?
If not please provide sample data in data format for all tables to replicate your model.
Thank you for your reply. I got it to work except for the circled column.
Here's how I arranged everything:
Rows: EmployeeInfo
Columns: Months (Single column)
Values: Planned, Actual
I have a table named Balance with the two columns: EmployeeID and LeaveBalance. I connected it to the Employee table this way:
EmployeeID in Employee table (1)-->(*) EmployeeID in Balance table
How do I get the Leave Balance to display as I want it in the image above?