Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
140 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
209 | |
92 | |
64 | |
59 | |
56 |