Visualise a Pivot Table with a few Fields dragged in the Report filter, Row labels and Value Area section.  In the Column labels are two fields, Month and then Year - so in the column labels, for every month, there is data for three years 2005, 2006 and 2007.  For some months, there is data for two years only 2005 and 2006.  In the Value area section are fields such as Net Amount, Quantity, Bonus etc. and the summarization function applied to them is SUM.  There is no complication in creating the Pivot Table described above.

The actual requirement is to customise the Subtotal column of the Pivot Table as follows:

1. For the monthly subtotals, the Net Amount and Bonus figure are to computed as a difference of 2005 and 2006 i.e. SUM of quantity of 2005 - SUM of quantity of 2006.  The Grand total column should be a a summation of individual subtotals.
2. Average Selling price for every year is to be computed as as Net Amount/(Ttl Bonus + Quantity).  For the monthly subtotals, the figure is to be computed as

=(Net Amount of 2005/((Bonus of 2005+Quantity of 2005)) - (Net Amount of 2006/((Bonus of 2006+Quantity of 2006))

The Grand Total column is to be left blank for Average Selling Price,

As you can observe, the subtotal column (for the months) will have different formulas running for different Fields.

A conventional Pivot Table does not allow one to have custom formulas in the Subtotal columns.  I have been able to resolve this problem by using the free Power Pivot add-in from Microsoft for Excel 2010 and higher versions.

You may refer to my solution in this workbook.

Here's another example.  Assume a dataset with three columns - Date, Manager and Amount.  There are repetitions in the Data and Manager column.  One may want to know the maximum amount per month per Manager.  While this is easy to accomplish with a Conventional Pivot Table as well, the problem occurs in the Subtotal/Grand Total cells of a Pivot Table.  The Subtotal/Grand Total cells assume the same function as has been used in the "Summarise Values field by".  So, while in the "Summarise values fields by" section, one may want to use the Maximum function, in the subtotal cell, one may want to use the sum function.

You may refer to my solution in this workbook.

1. Hello,

please have a look at my table:
http://1drv.ms/TlfVdp

I am trying to create a pivot table that allows me to give me an overview of all my products being sold in various countries and cities. I want to have all competitors to be displayed in the table so that I know exactly in which particular city of a country I compete with which suppliers.

As an additional feature, I need the last row ‘grand total’ to show the sum of all my product prices whereas the last column ‘grand total’ shall simply list the respective product price.

Problems that I encountered:

The competitors C1-C3 show numbers (product prices) but instead should be ‘x’ instead. When I change the number format to ‘x’, the grand total becomes ‘x’ as well.

As I have multiple entries per product, product prices are added up automatically and thus give me incorrect values (prices).

Thanks.

• Hi,

I have been able to solve this with the help of a Calculated Field formula in Power Pivot. You may refer to my solution in this workbook.

Hope this helps.

• Hi,

Now I have an ever-changing table so it would be of great help to know the steps you took in order to get to the solution you provided.

Thank you!

• Hi,

This is what I did

1. Selected the Data Range (A1:F40 of sheet1) and went to PowerPivot > Add to Data Model
2. In the Power Pivot, I went to Home > PivotTable > PivotTable
3. I dragged Customer, Country and City to Report Filter, Product to Row labels and Competitor to Column labels section
4. I then went to PowerPivot > Calculated Fields > New Calculated Field and wrote the formula there

Hope this helps.

2. vaibhav moona

Hello sir,

Its an urgent query. i have created a pivot table where rows have four calculated item fields. first 3 are calculating a number, fourth is calculating a percentage of above 2 calculated item field. Columns are months which can be grouped to year. for every individual month, percentage is coming accurately. But in the subtotal column or when i group months to year, like other numbers, percentage is also getting summed up and showing market share as 500% etc. etc. how to correct this issue?

3. vaibhav moona

I cant data from office. (my source data contains suppose year, month, brand, volume). when i make pivot of this data, i keep (year and month under column labels, brand under rows, sum of volume under values). for individual month its calculating market share correctly, but upon grouping and in subtotal column, its adding up all the individual month market shares. because brands are directly coming up from the source table, i can seperately use calculated item to calculate total and then change the solving order. But in case brands are itself calculated items, it wount accept calculating total of that. please suggest.

• Hi,

• Hi,

Here's another way. If you wish to solve this problem in versions of MS Excel older than Excel 2007, then you will have to use MS Query to transform the dataset i.e. show one column for each Brand.

Here's a short video

http://youtu.be/iwzt2YvU6WQ

4. Hi Sir,

• Hi,

This problem is arising because of the Calculated Items formulas that you have had to write. I can think of two solutions to this problem:

1. PowerPivot - We can simple PowerPivot formulas to obtain your desired results. Please refer worksheet named "PowerPivot solution". Since I have created this in Excel 2013, you will be able to play around with this Pivot Table only if you are also using PowerPivot in Excel 2013 i.e. even if you install PowerPivot in Excel 2010, you will not be able to play with the Pivot Table (though you will be able to view the result). In Excel 2007, you should be able to view the result (even without the PowerPivot installed).

2. Power Query and Pivot Tables - Using Power Query (a free add-in from MS for Excel 2010 and higher version), I have been able to reorient your dataset to show one column per Brand instead of all brands appearing in one column (Refer worksheet named "Result of Power Query"). Please download and install Power Query. This reoriented data structure allowed me to write Calculated Field formulas (instead of Calculated Items formulas) - refer worksheet named "Pivot Table from Power Query".

Hope this helps.

• Vaibhav Moona

Hi sir,

Thanks for solving the problem. I was very easily able to solve the issue by using power query in excel. I was struggling since long, but with your solution things turned up very fast. Thanks for the help.

5. Hi Ashish,
I just downloaded power pivot in Excel 2010. I have never used it before and would like to show the grand total of ALL items when using the filter to top 10 (or whatever I select) option.

For example, please see below link. I would like to show the grand total of ALL hair dryers in D8, not just the total of what I filtered to. Also how could I show both? Thanks so much for your help Ashish, you have no idea how much time this will save my company.

https://onedrive.live.com/redir?resid=94EAC9CD10E8F71F!798&authkey=!AChKTe8CYjbZ_hw&ithint=file%2cxlsx

Thanks!

• Hi,

You may refer to my solution in the "PowerPivot solution" worksheet in this workbook. For your reference, I have written the following Calculated Field formula

Total Revenue = if(HASONEVALUE(Data[ARTICLE]),SUM(Data[CUR \$]),CALCULATE(SUM(Data[CUR \$]),ALLEXCEPT(Data,Data[CATEGORY],Data[ITEM STATUS])))

Hope this helps.

6. Hi,
In your solution - calculated field is completely greyed out so I cannot even take a look at your formula. Thank you for providing it here - but I tried to insert this formula using calculated field and it did not work at all. Note - I didn't particularly go to the power pivot option - but did not see a calculated field option in there..... don't know anything about power pivot. Just downloaded it for the first time. Help !

• Hi,

This is what you have to do:

1. Select A1:E77 in your base data worksheet and press Ctrl+T > OK
2. Click on any cell in the range above and click on the Table Tools Yellow button in the Title bar of your Excel file
3. In the Table Name box (extreme left), type Data and press Enter
4. Select A1:E77 and under PowerPivot, go to "Add to Data Model". The terminology may be slightly different in your version (mine is Excel 2013)
5. In the PowerPivot window, go to Home > Pivot Table > Pivot Table
6. Drag Item Status to the Report filter and select Active
7. Drag Category, Article and Article Description to the Row labels
8. Now go to PowerPivot > Add measure
9. Now type the measure (renamed as calculated field formula in Excel 2013) which I shared below
10. Click on OK
11. Sort the Article column on Top 3.

Hope this helps.

7. Hi,
Thanks. I have excel 2010 and could not find any form of "add to data model". So I skipped that step. But it worked! Thank you so very much. What a time saver.

So nice of you to help.

• Hi,

You are welcome. This should also work

if(HASONEVALUE(Data[Article]),SUM(Data[CUR \$]),CALCULATE(SUM(Data[CUR \$]),ALL(Data[Article])))

8. Hi,
Thanks, sorry but my boss now would like to see it in a different way. How do I still show the subtotal of the filtered items, then the total of the "others" (which is the difference between the grand total and the filtered), then the final grand total?

I no longer need to filter to active only so I used the formula you just shared above. Please see my power pivot shared below. Hope you can solve this one too.

https://onedrive.live.com/redir?resid=94EAC9CD10E8F71F!830&authkey=!ANLHKC6wVaKCv7M&ithint=file%2cxlsx

9. Hi,
Disregard my above question. I found the answer here:

http://www.powerpivotpro.com/2014/11/displaying-top-n-bottom-n-and-all-others/

10. Can you email the workbook above please as i cannot open the above link, thanks

• Hi,

11. Nilesh Parulekar

Hi Ashish

Thank you for your blog it is very useful.
We are facing an issue in a pivot in which we thought you could help us.
We have a source sheet for excel pivot which has a calculated percentage field e.g " % age Variance". When we use it in pivot it calculates grand total incorrectly as pivot does a simple sum or average of percentages.
To tackle this we used a calculated field formula for the pivot column. It works correctly however as we have around 20k lines of data , it's ( calculated fields) performance is very poor.
Is there any other way in which we can achieve this?. Or can a power pivot help us give a better performance? Please note we are using excel 2013 .

• Hi,

12. Nilesh Parulekar

Hi Ashish,

Thanks a lot for getting back.
Here is a sample example i was quoting.

https://1drv.ms/x/s!AjTEbdEWD4FCgk6L5K02AU5HkTvT

• Hi,

These are the PowerPivot calculated field formulas I have written

Total =SUM(data[value])
Variance =(CALCULATE([Total],data[Key Fig]="KF1")-CALCULATE([Total],data[Key Fig]="KF2"))/CALCULATE([Total],data[Key Fig]="KF2")

Hope this helps.

• Nilesh Parulekar

Hi Ashish

Thanks a lot for checking out. Did you put the formulas in the excel link i provided.As, I did not find any in the excel. Will you please help me put the formulas in the sample excel so that i can clearly understand.

Regards

13. Nilesh Parulekar

Hi Ashish thanks again. Even I figured it out with your explanation. However, issue I face here is :
The variance is a derivation of KF1 and KF2, so how to restrict it to shown only one variance per set of KF1 and KF2 values.
In the excel you provided the variance is repeating per KF.

• Hi,

I completely overlooked that in my solution. Sorry about that. I think the only way to do that is to convert the PowerPivot result into CUBE formulas and then delete that column.

• Hi,

Refer to my solution in the worksheet named Ashish solution1 of this workbook.

Hope this helps.

14. Nilesh Parulekar

Hi Ashish

Thanks a lot!. It seems that your solution suggests we can now achieve the expected result even without converting to Cube formulas. Please correct if I am wrong.