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.

1. Ashish, thanks for this. It's very helpful.

2. allison martin

could you help me with my pivot table problem?

• Hi,

• allison martin

Trying to get a pivot table from the data in all the spreadsheets, combined. Really only concerned currently about data in B4(hidden):H4 and cells down.

• Hi,

• Hi,

I could download the file. Since the structure of data on all sheets is the same (columns B:F), you may refer to my solution at this link

Hope this helps.

• allison martin

Thanks for the email. I would prefer to use power query so I can use the data as a pivot table, however, I could not get Power Query to install on my computer. I tried the 2nd option, but because the consolidated file runs macros, it's unable to get past #9 in the 2nd solution. Any suggestions?

• Hi,

What problem are you facing when you use Power Query? Power Query is a free add-in from MS for Excel 2010 and higher versions. If you are facing some installation issue, then please post your query in the forums of Power Query.

3. Sales Forecasts should account for "price changes". Each individual product has a unique "price change". How can you use powerpivot to build Sales Forecasts that account for the unique "price change" of each product, for each year of the Sales Forecast?

• Hi,

You may refer to my solution in the "Performance" worksheet of this workbook.

Hope this helps.

4. Theresa

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.

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

• Hi,

You may refer to my solution in this workbook.