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] )
    )
)
v-chuncz-msft's avatar
v-chuncz-msft
Icon for Community Support rankCommunity Support
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

    Hi Sam,

     

    Thanks for looking into this. Could you explain how the measure works for my understanding? I have tried to use it and it seems to be calculating the wrong value. It looks like the distinct count is still counting all distinct weeks in the data (or at least the data relating to the selected staff manager in the visual), rather than just for each individual within the staff manager's team. Any suggestions on how I can get round this?

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

    In my above example, for instance, Manager B has two staff members (person 2 and person 4). Person 2 has target hours of 45 and has worked in all 5 weeks so their target hours should be (45 x 5). Person 4 has target hours of 35 and has only worked 3 weeks so their target hours should be (35 x 3). At the staff manager level this should aggreate to (45 x 5)+ (35 x 3). What my formula was doing, and I think yours, is to calculate the DISTINCTCOUNT of weeks at the staff manager level and return 5 in both cases. The result is (45 x 5)+ (35 x 5)- i.e. target 70 hours to high. Any idea how to get around this?

      • 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?