Forum Discussion

M_J_Farrow's avatar
M_J_Farrow
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Summarising Actual and Target Hours

Hi. I am trying to create a visual that summarises staff member actual hours and target hours aggregated to staff manager level. I will start by providing the relevant example details and then explai...
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    M_J_Farrow,

     

    You may refer to the following measure.

    Measure =
SUMX (
    VALUES ( Table2[Staff Member] ),
    CALCULATE (
        SELECTEDVALUE ( Table2[Target Hours] ) * DISTINCTCOUNT ( Table1[Week] )
    )
)
M_J_Farrow's avatar
M_J_Farrow
Icon for Advocate II rankAdvocate II
8 years ago

Thanks Sam. This does indeed work. Would you be able to talk me through how the formula works. I am an advanced Excel user but new to Power BI. My understanding of the formula is:

 

SUMX- consists of a table which a function is then performed on

VALUES- creates a table (not visible) of distinct staff members- this is input into the SUMX

CALCULATE- scans the table created using VALUES and takes the contractual hours for the individual and multipies this by the distinct count of that staff members weeks.

 

When I then put this is a staff manager table it creates a filter context allowing me to see this information aggregated to staff manager.

 

Am I correct in my understanding?