Hello
I would like to create a table matrix that should contain the following Categories.
Project Name
Employee Name
Role Name
The above categories are all from seperate dimension tables. I am trying to calculate the percenatge of worked hours from the Project Name only. Below Table is the result I would like, but at the moment with this measure I get 100% in a cells except for the Total Column.
When I add the measure "Total Allocated Hours" and change the value to %GT Total Allocated Hours, then it works when filtered to individual Projects, but when I remove the filter, it calculates over the whole table. I would like something similar, but it stays the same within the project category.
Assignment % =
VAR total_Allo_Hours = [Total Allocated Hours]
VAR hours_p_role = Calculate([Total Allocated Hours], ALLSELECTED(Roles[name]))
VAR hours_p_user = Calculate([Total Allocated Hours], ALLSELECTED(Users[Employee]))
VAR hours_p_project = Calculate([Total Allocated Hours], ALLSELECTED(Projectroles[Project_Name]))
Return
If(ISINSCOPE(
Roles[name]), DIVIDE([Total Allocated Hours], hours_p_role),
if(ISINSCOPE(
Users[Employee]), DIVIDE([Total Allocated Hours], hours_p_user),
if(ISINSCOPE(
Projects[Project_Name]), DIVIDE(total_Allo_Hours, hours_p_project))))
| | | Role 1 | Role 2 | Total % |
| Project Name | Employee | | | |
| Pro_1 | Emp1 | 25% | | 25% |
| | Emp2 | | 25% | 25% |
| | Emp3 | 25% | 25% | 50% |
Any help would be appreciated
