A Measure or a Relationship?

[Marna]

Hi

I've been struggling with the following for the last week and haven't reached a solution yet. I'm new to power pivot and is trying to create a power pivot. I have the following information:

Columns:

Employee Personnel Code, Employee's name, job category, Month name (Jan-13 to Dec-14) with a lab ID assigned to every employee per month, month name (Jan-13 to Dec-14) with the employees' gross salary per month.

The lab ID is associated to the department where the employee was working for a certain month and that can change from month to month. I also have other salary components like overtime, but I first have to figure out this problem, before I can continue with the others. That's the reason I thought I would need a power pivot.

Here is a sample of the first 5 rows.

Table name: Basic
[TABLE="width: 1348"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col span="2"><col span="12"></colgroup><tbody>[TR]
[TD]Employee ID[/TD]
[TD]Employee Name[/TD]
[TD]Job Category[/TD]
[TD]Jan-13[/TD]
[TD]Feb-13[/TD]
[TD]Mar-13[/TD]
[TD]Apr-13[/TD]
[TD]May-13[/TD]
[TD]Jun-13[/TD]
[TD]Jul-13[/TD]
[TD]Aug-13[/TD]
[TD]Sep-13[/TD]
[TD]Oct-13[/TD]
[TD]Nov-13[/TD]
[TD]Dec-13[/TD]
[TD="align: right"]Jan-13[/TD]
[TD="align: right"]Feb-13[/TD]
[TD="align: right"]Mar-13[/TD]
[TD="align: right"]Apr-13[/TD]
[TD="align: right"]May-13[/TD]
[TD="align: right"]Jun-13[/TD]
[TD="align: right"]Jul-13[/TD]
[TD="align: right"]Aug-13[/TD]
[TD="align: right"]Sep-13[/TD]
[TD="align: right"]Oct-13[/TD]
[TD="align: right"]Nov-13[/TD]
[TD="align: right"]Dec-13[/TD]
[/TR]
[TR]
[TD]6076[/TD]
[TD]Mev E BOTHA[/TD]
[TD]Admin[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]8,672[/TD]
[TD]9,606[/TD]
[TD]9,606[/TD]
[TD]9,606[/TD]
[TD]9,606[/TD]
[TD]9,606[/TD]
[TD]9,606[/TD]
[TD]9,606[/TD]
[TD]9,606[/TD]
[TD]9,606[/TD]
[TD]9,606[/TD]
[TD]9,606[/TD]
[/TR]
[TR]
[TD]9001[/TD]
[TD]Mev J MASALESA[/TD]
[TD]Admin[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]Ad[/TD]
[TD]4,604[/TD]
[TD]5,104[/TD]
[TD]5,104[/TD]
[TD]5,104[/TD]
[TD]5,104[/TD]
[TD]5,104[/TD]
[TD]5,104[/TD]
[TD]5,104[/TD]
[TD]5,104[/TD]
[TD]5,104[/TD]
[TD]5,104[/TD]
[TD]5,104[/TD]
[/TR]
[TR]
[TD]10094[/TD]
[TD]Miss E DOS SANTOS[/TD]
[TD]Technologist[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]0[/TD]
[TD]9,495[/TD]
[TD]9,495[/TD]
[TD]9,495[/TD]
[TD]9,495[/TD]
[TD]9,495[/TD]
[TD]9,495[/TD]
[TD]9,495[/TD]
[TD]9,495[/TD]
[TD]9,495[/TD]
[TD]9,495[/TD]
[TD]9,495[/TD]
[/TR]
[TR]
[TD]8067[/TD]
[TD]Mev M VAN ROOYEN[/TD]
[TD]Registered Nurse[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]U[/TD]
[TD]U[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]8,759[/TD]
[TD]9,697[/TD]
[TD]9,697[/TD]
[TD]9,697[/TD]
[TD]9,697[/TD]
[TD]9,697[/TD]
[TD]9,697[/TD]
[TD]9,697[/TD]
[TD]9,697[/TD]
[TD]9,697[/TD]
[TD]9,697[/TD]
[TD]9,697[/TD]
[/TR]
[TR]
[TD]8121[/TD]
[TD]Mev J LINDEQUE[/TD]
[TD]Technologist[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]U[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]E[/TD]
[TD]10,200[/TD]
[TD]10,710[/TD]
[TD]10,710[/TD]
[TD]10,710[/TD]
[TD]10,710[/TD]
[TD]10,710[/TD]
[TD]10,710[/TD]
[TD]10,710[/TD]
[TD]10,710[/TD]
[TD]10,710[/TD]
[TD]10,710[/TD]
[TD]10,710[/TD]
[/TR]
</tbody>[/TABLE]

I want to create a pivot table that summarizes the job categories per department per month.

Something like this:


[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Dept name[/TD]
[TD]Job category[/TD]
[TD]Gross
Jan-13[/TD]
[TD]Gross
Feb-13[/TD]
[/TR]
[TR]
[TD]Ad[/TD]
[TD]Admin[/TD]
[TD]13 267[/TD]
[TD]14 710[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]Technologist[/TD]
[TD]0[/TD]
[TD]9 495[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Registered nurse[/TD]
[TD]8 759[/TD]
[TD]9 697[/TD]
[/TR]
[TR]
[TD](also under E)[/TD]
[TD]Technologist[/TD]
[TD]10200[/TD]
[TD]10710[/TD]
[/TR]
</tbody>[/TABLE]





.


I created another table for all job catergories and another for the department names and then tried relating them to each other. I succeeded in that. When I tried to relate table "Basic" to the department names, I had to use a unique column. The only unique column was one of the months (I used Jan-13). When I did that, the sum of gross salaries always related to that month's department selection. So, if an employee worked at another department in another month and I want to calculate the gross salary for another month (Feb-13), the pivot table refers to Jan-13 for the department names and the gross salary month to Feb-13. I wanted it to look at Feb-13 for the Department selection and at Feb-13 for the gross salaries.

I tried using a DAX formula (calculate) and that was working in a way, but not what I was looking for.

Could you please point me in the right direction. I would really appreciate it.

Thanks in advance

Regards

Marna