Calculate the sum of average values in Pivot Table

[andymanu]

Hi,

Got a question and need to get my head around how to fix it.

I got some employee data set looks like below (Table 1).

Employee No	Employee Name	Wage Category	Hours Worked
1	Andy	Ordinary Hours	50
1	Andy	Annual Leave	10
1	Andy	Sick Leave	5
2	David	Ordinary Hours	60
2	David	Annual Leave	15
2	David	Sick Leave	5
3	Mary	Ordinary Hours	55
3	Mary	Annual Leave	1
3	Mary	Sick Leave	2
4	Sue	Ordinary Hours	52
4	Sue	Annual Leave	12
4	Sue	Sick Leave	3

The above table demonstrates the actual hour of wage category data for 4 employees for a fortnight.

In addition to above, I got another table which looks like below and it got data for employee contracted hours for a fortnight (Table 2).

Employee No	Contracted Hours
1	70
2	70
3	50
4	72

Requirement;

I want to compare the total wage category hours against the contracted hours for each employee. Thus, during the data loading process, I used "Left Join" command to add the contracted hours of data to the Table 1 data.

Now the final data set looks like below;

Employee No	Employee Name	Wage Category	Hours Worked	Contracted Hours
1	Andy	Ordinary Hours	50	70
1	Andy	Annual Leave	10	70
1	Andy	Sick Leave	5	70
2	David	Ordinary Hours	60	70
2	David	Annual Leave	15	70
2	David	Sick Leave	5	70
3	Mary	Ordinary Hours	55	50
3	Mary	Annual Leave	1	50
3	Mary	Sick Leave	2	50
4	Sue	Ordinary Hours	52	72
4	Sue	Annual Leave	12	72
4	Sue	Sick Leave	3	72

In the pivot table, I created a measure named "Contracted Hours" and the calculated expression is "Avg([Contracted Hours])"  and for each Wage Category item a separate measure of:

Sum(If([Wage Category ]='Ordinary Hours',[Hours Worked]))

Sum(If([Wage Category ]='Annual Leave',[Hours Worked]))

Sum(If([Wage Category ]='Sick Leave',[Hours Worked]))

Question;

I want to show the "Total" function of the pivot table and if I use it, all the Wage Categories will show the correct total value for each Wage Category except for the " Contracted Hours" because my calculation expression is "Average".

Appreciate your feedback on a method to overcome this issue in my data processing model.

The pivot table I have done in Excel and will looks like below;

Row Labels	Contracted Hours	Hours Worked
Andy	70	65
David	70	80
Mary	50	58
Sue	72	67
Grand Total	65.5	270

Thank you in advance.

Sorry for the length of the question.

Kind regards,

Andy

[SergeyMak · Accepted solution]

Hi Andy,

I think you need to concatenate Contracted Hours, but not join them.

Although you  need to prepare data first.

//0. I assume you have loaded Table 1 in memory.

//1. Create a map with Employees

MAP_EmpID2Employee:

MAPPING LOAD [Employee No], [Employee Name]

Resident [Table 1];

//2. Prepare you data when you load Table 2 and concatenate it with table 1

Concatenate([Table 1])

LOAD 

[Employee No],

ApplyMap('MAP_EmpID2Employee', [Employee No],'No match in Table 1 for ' & [Employee No]) AS [Employee Name],

'Contracted Hours' AS [Wage Category],

[Contracted Hours] AS [Hours Worked]

FROM YOUR_DATA_SOURCE;

Then you can use Expression

For Contracted SUM ({$<[Wage Category]={'Contracted Hours' }>}[Hours Worked]) 

For Hours Worked SUM ({$<[Wage Category]-={'Contracted Hours' }>}[Hours Worked]) 

Hope it helps

[andymanu]

Hi Sergey,

Thanks a lot for your reply.

Yes, the Concatenate function does the job instead of using the Join function.

Great.

Kind regards,

Andrew