Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Helper IV
2 years ago
Solved

Project Manager for most hours worked

Hi

Can anybody help, I am attempting to return in a measure the 'Project Manager' for whom a technician has worked the most hours for by division to be displayed in a table visual.

I have attached a sample pbix here toppm.pbix 

 

The measure I have written so far is

 
EU Project Manager =

VAR temp_table =
    SUMMARIZE (
        ALL ( FactTimesheet[ProjectManager] ),
        FactTimesheet[ProjectManager],
        "HoursWorked", CALCULATE(SUM ( FactTimesheet[Duration] ), FactTimesheet[Division] = "EU")
    )
RETURN

    CALCULATE (
        MAX ( FactTimesheet[ProjectManager] ),
        FILTER (
            ADDCOLUMNS ( temp_table,"Rank", RANKX ( temp_table, [HoursWorked] ) ),
            [Rank] = 1
        )
    )
But this returns a value even if a technician has worked no hours for the 'EU' division

Any help appreciated to solve
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Richard_Halsall ,

    I updated your sample pbix file(see the attachment), please find the details in it.You can create a measure as below to get it

    Project Managers for most hours worked = 
VAR _tab1 =
    SUMMARIZE (
        'FactTimesheet',
        'FactTimesheet'[Technician],
        'FactTimesheet'[Division],
        'FactTimesheet'[ProjectManager],
        "@Duration", CALCULATE ( SUM ( 'FactTimesheet'[Duration] ) )
    )
VAR _tab2 =
    ADDCOLUMNS (
        _tab1,
        "@rank",
            RANKX (
                FILTER (
                    _tab1,
                    [Technician] = EARLIER ( 'FactTimesheet'[Technician] )
                        && [Division] = EARLIER ( 'FactTimesheet'[Division] )
                ),
                [@Duration],
                ,
                DESC
            )
    )
RETURN
    MAXX ( FILTER ( _tab2, [@rank] = 1 ), [ProjectManager] )

    Best Regards

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Richard_Halsall ,

    What's your expected result? Do you want to get the project manager who has the most durations by division? Please check the screenshot below,  the project managers(AS, CG and GW) are what you want? 

    Best Regards

    • Richard_Halsall's avatar
      Richard_Halsall
      Helper IV
      2 years ago

      Anonymous Hi, I have mocked up below what I am hoping to achieve. For each contractor I need to see the project manager for which they worked the most hours by division

       



      If in the data you look at technician a024H00000maqK5QAI they have worked in the EU division for:

      CG - 11.66 hours

      DS - 9.73 hours

      GW - 55.54 hours

      So I would expect to see GW returned for the EU project manager

       

      I hope this provides a clearer explaination of requirements

       

      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Richard_Halsall ,

        I updated your sample pbix file(see the attachment), please find the details in it.You can create a measure as below to get it

        Project Managers for most hours worked = 
VAR _tab1 =
    SUMMARIZE (
        'FactTimesheet',
        'FactTimesheet'[Technician],
        'FactTimesheet'[Division],
        'FactTimesheet'[ProjectManager],
        "@Duration", CALCULATE ( SUM ( 'FactTimesheet'[Duration] ) )
    )
VAR _tab2 =
    ADDCOLUMNS (
        _tab1,
        "@rank",
            RANKX (
                FILTER (
                    _tab1,
                    [Technician] = EARLIER ( 'FactTimesheet'[Technician] )
                        && [Division] = EARLIER ( 'FactTimesheet'[Division] )
                ),
                [@Duration],
                ,
                DESC
            )
    )
RETURN
    MAXX ( FILTER ( _tab2, [@rank] = 1 ), [ProjectManager] )

        Best Regards