Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to Solution.
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
vResult
In 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.
Proud to be a Super User!
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
vResult
In 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.
Proud to be a Super User!
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 )
)
Proud to be a Super User!