Adding data to the rows section of the Pivot

[tommymac]

Hi All,

I am hoping someone can help me, I have an issue where ideally I would like to use something from a data table in the rows section of a pivot table. I have created a simplified version of my model to recreate the issue.

https://drive.google.com/open?id=0B_IcbwuqJSO4VmpMOEM5STE2Ukk

Essentially I have 2 tables, set out as follows;

Table 1 (LookupJobs table) contains a list of unique jobs with the following columns;
job number (unique)
customer
site
date
revenue

Table 2 (DataTimesheets table) is essentially a consolidated timesheet for all jobs with the following columns;
Job number (relationship w/table 1)
Employee Name
Hours Worked

In addition I have 2 measures;
A sumx on revenue and,
A sumx on hours worked

Then the pivot table is set out as follows;

Rows;
Job Number (from table 2)
Customer (from table 2)
Employee Name (from table 1)

Values;
Total Revenue (measure)
Total Hours Worked (measure)

---

I thought at first the issue was because I could not add something from table 1 into the rows section of the pivot table. However upon typing this up and thinking about it, it could be a combination of that plus the fact that the revenue is at job level and I am trying to take the report down to employee level?

Thank you in advance to anyone that can help or point me in the right direction.

Regards,

 

[tallan]

For starters, you have a granularity issue with numbers in your lookup table and numbers in your data table (especially since you are putting them both in the values section of the pivot). You should have a separate table for revenue (in parallel to the table for hours worked) with columns Job Number, Date and Revenue. Surprised also that you do not have a date column in your table for hours worked.

Here is a good place to start with model design.

Your specific issues are covered in chapters 2, 3 and 9 of this book: Analyzing Data with Power BI and Power Pivot for Excel (Business Skills): Alberto Ferrari, Marco Russo: 9781509302765: Amazon.com: Books

Tom
PowerPivotPro - Transforming your Business with Power Pivot and Power BI