Create a Pivot Table from multiple individual ranges without using ancillary columns

{ 16 Comments }

Assume that there are three separate tables showing the following information:

1. Date of visit data for visitors to a certain recreation facility.  The same visitor may visit the facility multiple times
2. Fee per visit in different cites
3. Region in which each city lies

The question is to analyse the three tables above via a Pivot Table to generate the following:

1. Region wise and visitor wise:
a.  Fee per visit
b.  Frequency of visit
c.  Revenue
2. Revenue collected by month wise and by visitor

In this workbook, I have shared two solutions:

1. PowerPivot solution – This solution answers both questions above without using any calculated/ancillary columns in the base data.  By establishing simple relationships in the PowerPivot window and by writing two calculated Field formulas, both questions above have been answered.  To use this PowerPivot solution, you need to be using the PowerPivot add-in for MS Excel.  This add-in is only available for Excel 2010 and higher versions.

2. Pivot Table solution – This solution answers both questions above by using calculated/ancillary columns in the base data.

Here’s another example.  Assume a four column table showing Date of session, Client, Location and Participant Name.  Assume another four column table showing Client, Date of session, Date of invoice and Amount Billed.  The task is to determine the Amount billed per location.  You may refer to my PowerPivot and Pivot Table solution in this workbook.

Leave a Comment

Your email address will not be published.

*

  • I have two worksheets with raw data that is automatically generated by our systems, I cannot edit or change anything in the raw data. I must use this data to compile a forecast versus actual employee trending, meaning in forecast I have “x” amount of people who are allotted these for these many hours for the pay rate they have and compare that with how much the resources were actually paid and logged for hours. I had tried to do this with power pivot, but the problem I’m running into is that I don’t have a “primary key”, where only one value is assigned to one employee. Another problem I have is the number of forecasted employees may be more or less than the actual employees I have with different names and such. I ended up doing it manually which takes a lot of time, I would like to automate this.

    Link to spreadsheet:

    https://onedrive.live.com/redir?resid=92A0C40845606F21!304&authkey=!ALdNpIxTduHRmnE&ithint=file%2cxlsx