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?