I'm having some trouble using a sheet as filter in a dashboard where I have sheets from two different data sources.
I'm analyzing internal projects that have atributtes such as area, project leader and that have an internal hierarchical structure of milestone, tasks, tasks of tasks and in the lowest level there are activities. So one project is build of activities that belongs to one task of task, that belongs to one task, that belongs to one milestone. Since the project's data source I'm using is the one where we check the status of each project we have one additional field that is the progress of the project in general and of the particular components of it. To simplify I just add here the general progress of the project because is the measure I'm interested in now.
Due to the structure of my data, the progress of a project is repeated as much as activities (the lowest level of a project's structure) the it has as you can see in example table 1 attached.
On the other hand, we have another data source whit the budget for each project, so each project is a record with its budget (as you can see in example table 2 attached).
I have a dashboard with a summarize view of projects where I want to see the overall budget and the average progress of each area's and project leader's projects. At the same time I want to use the sheets with the average progress of each area's and project leader's projects as filter in the dashboard so I can see the budget for the area or leader selected.
The problem I'm facing is that as the only field in common that both data sources have is project id I need to have that field in the visualisation I want to use as filter in the dashboard, but if I included in the detail mark it changes my visualisation and my bar graph doesn't represent the average progress of area or leader anymore. I can't understand why when I include the project the length of the bars change to the sum of the average progress of each project instead of remain as the average progress of the area's or leader's projects.
Any help will be really appreciated, maybe I'm misunderstanding the way I have to use actions in dashboard or how the detail marks behave, or maybe it's not the detail mark the one I have to use to make it appears the field both data sources have in common.
I attach two examples of my data sources and one packaged workbook when you can see three different sheets:
1. Average progress of area's projects
2. Average progress of leader's projects
3. Total budget of projects
And the dashboard when I want to use the first two sheets as filter to all the rest.
As I mentioned, the project's progress are repeated because of the structure one of the data sources, so I have to use a LOD expression to get the project's average progress. Maybe this is also something to check.
Thanks in advance!