Filtering hours table based on projectmanager in the projectstable...difficult ;)

[BobKoenen]

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?

[DataInsights · Accepted solution]

@BobKoenen,

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.

[DataInsights]

@BobKoenen,

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 )
)