Summarising Actual and Target Hours

[M_J_Farrow]

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 explain where my issue has occured.

I am currently using two Excel tables as data sources for my Power BI model:

1) A data source that shows staff member actual hours booked by week. The data source actually has a variety of different data types within it, but for our purposes we can summarise it to something that looks like the below:

Staff Member	Week	Actual hours
Person 1	Week 1	20
Person 1	Week 1	7.5
Person 1	Week 1	10
Person 1	Week 2	22
Person 1	Week 2	15.5
Person 1	Week 3	37.5
Person 1	Week 4	21
Person 1	Week 4	4
Person 1	Week 4	7
Person 1	Week 4	5.5
Person 1	Week 5	18
Person 1	Week 5	19.5
Person 2	Week 1	46
Person 2	Week 2	81.5
Person 2	Week 3	48
Person 2	Week 4	47.5
Person 2	Week 5	44.5
Person 3	Week 1	12
Person 3	Week 1	4
Person 3	Week 1	21.5
Person 3	Week 2	24
Person 3	Week 2	14
Person 3	Week 3	37.5
Person 3	Week 3	3
Person 3	Week 4	39
Person 4	Week 3	35
Person 4	Week 4	23
Person 4	Week 4	14
Person 4	Week 5	37.5

The main thing to note about this data is that for various staff members may not book every week. They may have left the company during the year, joined late etc. This is where my problem comes and will be discussed later. they may also make more than one booking in a week (e.g. because they worked on two seperate projects).

2) A mapping table which contains data about the staff members' staff managers and target hours (among other things). The two tables have a relationship based on a unique identifier number for each staff member, which I have not included as this part is working fine.

Staff Member	Staff Manager	Target Hours
Person 1	Staff Manager A	37.5
Person 2	Staff Manager B	45
Person 3	Staff Manager C	30
Person 4	Staff Manager B	35

From hear creating my intial visual was easy. the relationship between the two tables meant I could create a matrix visual showing actual hours per staff manager

Row Labels	Sum of Actual hours
Staff Manager A	187.5
Staff Manager B	377
Staff Manager C	155
Grand Total	719.5

Now comes the issue. I wanted to add another column showing target hours to date (again at the aggregated staff manager level). My initial approach was simple but flawed:

1) create a measure using SUM which would sum target hours.

2) create a measure identifying number of weeks using DISTINCTCOUNT (this is where the issue appears)

3) create a measure multiplying 1 by 2 to show aggregated target hours at the staff manager level.

The problem that occurs is that distinct count will return the maxium number of distinct weeks within the data aggregated at staff manager level, not the number of weeks booked by each staff member. In the example to date this means that staff manager A will show the incorrect target hours as he has two staff members (person 1 and person 4). Person one has worked 5 weeks so the formula correctly calculates their target hours. Person 2 however has only worked 3 weeks. The formual multiplies their target hours by 5 and return the wrong result.

What I therefore need is a formula that will calculate target hours per staff member, iterating one staff member at a time and looking at the amount of weeks they actually booked and multiplying this by their target hours. The visual will then aggregate this at staff manager level. I presume I need to use the CALCULATE function to achieve this by changing the filter context, but having only just started using Power BI I'm not sure how to achieve this. Any advice would be greatly appreciated.

[v-chuncz-msft · Accepted solution]

@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]

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?

[v-chuncz-msft]

@M_J_Farrow,

Give it a try. It works.

[M_J_Farrow]

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?

[M_J_Farrow]

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?