Forum Discussion
Filtering hours table based on projectmanager in the projectstable...difficult ;)
HI all,
I am breaking my mind over this issue, I hope you can help me. My data looks like this
Data/fact Hourstable
| Employee | Projectcode | Hours | date |
| Jane | A | 4 | 5-5-2020 |
| Joe | A | 3 | 5-5-2020 |
| Jane | B | 2 | 6-6-2020 |
| Bill | C | 5 | 6-5-2020 |
| Jane | C | 3 | 8-7-2020 |
| Joe | B | 5 | 1-9-2020 |
I have 3 linked dimension tables: Employees, Projects and a Calendar. The projecttable is linked with the Hours via the ProjectCode
The project table has the following columns
| Projectcode | ProjectManager |
| A | Joe |
| B | Joe |
| C | Jane |
Now I want to create a measure which gives me the amount of hours the ProjectManager has spend on the project. The output I want to visualise in a table that should look like this
| Projectcode | ProjectmanagerHours | All Hours |
| A | 3 | 7 |
| B | 5 | 7 |
| C | 3 | 8 |
I cannot seem to Create the ProjectmanagerHours measure because I already linked the project table based on the Projeccode.
Can you help me?
Anonymous,
Try these measures:
All Hours = SUM ( Hours[Hours] ) Project Manager Hours Calc = VAR vProjMgr = MAX ( Projects[Project Manager] ) VAR vResult = CALCULATE ( [All Hours], Hours[Employee] = vProjMgr ) RETURN vResult Project Manager Hours = VAR vTable = ADDCOLUMNS ( SUMMARIZE ( Projects, Projects[Project Code] ), "tmpProjMgrHours", [Project Manager Hours Calc] ) VAR vResult = SUMX ( vTable, [tmpProjMgrHours] ) RETURN vResultIn the table visual, add Projects[Project Code] and the measures [Project Manager Hours] and [All Hours]. The measure [Project Manager Hours] (which is based on measure [Project Manager Hours Calc]) is necessary in order to calculate totals correctly.
2 Replies
- DataInsights
Super User
Anonymous,
Try these measures:
All Hours = SUM ( Hours[Hours] ) Project Manager Hours Calc = VAR vProjMgr = MAX ( Projects[Project Manager] ) VAR vResult = CALCULATE ( [All Hours], Hours[Employee] = vProjMgr ) RETURN vResult Project Manager Hours = VAR vTable = ADDCOLUMNS ( SUMMARIZE ( Projects, Projects[Project Code] ), "tmpProjMgrHours", [Project Manager Hours Calc] ) VAR vResult = SUMX ( vTable, [tmpProjMgrHours] ) RETURN vResultIn the table visual, add Projects[Project Code] and the measures [Project Manager Hours] and [All Hours]. The measure [Project Manager Hours] (which is based on measure [Project Manager Hours Calc]) is necessary in order to calculate totals correctly.
- DataInsights
Anonymous,
Here's a simpler solution. Measures below:
All Hours = SUM ( Hours[Hours] ) Project Manager Hours = SUMX ( Projects, VAR vProjMgr = Projects[Project Manager] RETURN CALCULATE ( [All Hours], Hours[Employee] = vProjMgr ) )