I have a set of records (projects) each with four attributes: Unique Project ID, Region, Completion Date (also broken out as quarters), and Project Type. Another table has a goal of project count indicating how many projects should be completed in a quarter. The Goals table has four attributes: Project Type, Region, Quarter and Goal.
I have made a matrix of the Projects table with the rows Region then Project type. The columns are the quarters in which the completion date falls. The value is the count of Project ID
Project Table Matrix View
Region North Q1 Count Q2 Count Q3 Count Q4 Count
Project Type A 6 5 5 7
Project Type B 3 9 1 3
Project Type C 2 2 8 7
Region South ...
Goals Table Matrix View
Region North Q1 Goal Q2 Goal Q3 Goal Q4 Goal
Project Type A 6 5 5 7
Project Type B 3 9 1 3
Project Type C 2 2 8 7
Region South ...
I want to add in the goal from the Goals table so that I can calculate how far we are from the goal.
Region North Q1 Count Q1 Goal Q2 Count Q2 Goal ....
Project Type A 6 5 5 7
Project Type B 3 9 1 3
Project Type C 2 2 8 7
Region South ...
Is there any way to do that? For instance, I can merge Type, Region and Quarter and use that to create a relationship between Goals and Projects but I still can't get that goals column into the matrix and behave correctly. If I add it as a number it forces me to summarize the goal as a count or something, the option to 'don't summarize' disappears. The goal will always be the same per Type, Region and Quarter but the count will change as we complete projects.
I hope I'm explaining this well enough. I'm wondering if anyone else out there has a similar issue. Any guidance is appreciated!
