Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
Show and Tell
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    5 years ago

    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
    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's avatar
DataInsights
Super User
5 years ago

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
    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's avatar
DataInsights
Super User
5 years ago

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