Tags: VALUE

Perform different calculations in the Subtotal/Grand Total column of a Pivot Table

{33 Comments}

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.

Compute year wise weighted average on a large dataset

{2 Comments}

Assume a dataset with a Key Performance Indicator (KPI) [appearing in one column] data for years ranging from 1985 to 2010 for 114 countries.  This dataset has 170,000 rows of data and one row below the last row for every country, there is a total of the KPI column.  So, if there are 25 rows for India, then in the 26th row, there will be the total appearing for numbers in the KPI column.  The same is occurring for other countries as well.

There is another dataset (in another worksheet of the same workbook) which has an index value for the same countries and same date range as the first dataset.  The second dataset is relatively smaller (with only 1315 rows) because the index value is not available for all years of each country.

The objective is to determine the year wise (for all years from 1985 to 2010) weighted average of KPI.  An illustration of the weighted average computation has been shown in range F5:H10 of the "Index" worksheet of the workbook link shared below.

Solving this problem using Pivot Table, filters, formulas will slow down processing speed due to sheer size of data.  I have solved this problem using the Power Pivot tool (for Excel 2010 and higher versions).

You may refer to my solution in this workbook.

Calculate a unique count with conditions in a Pivot Table

{12 Comments}

Assume a three column table arranged as follows: Circle, Date of Fault and ID.  Dates in the date range span one week - November 26, 2012 to December 2, 2012.  A particular equipment can be only one specific Region and the same equipment an go faulty multiple times within one week.  Data for one week is about 8,400 rows.

There are three questions to be answered from this data:

1. The Circle wise, count of ID's which went faulty more than twice between November 26, 2012 and December 2, 2012; and
2. The Circle wise, count of faulty instances more than twice between November 26, 2012 and December 2, 2012; and
3. Determine individual sites for 1 and 2 above

The difference between 1 and 2 above is "If a certain ID goes down 4 times, then for question1, the answer should be 1.  For question2, the answer should be 4."

The first question basically boils down to "Count of unique ID's by Circle which went faulty more than twice."

There are two ways one can go about answering the questions above:

Solution A - For Excel 2010 and higher versions - This solution is for those using the PowerPivot MS Excel add-in for Excel 2010 and higher versions.

Solution B - For all versions of MS Excel - This solution will work in all versions of MS Excel but for those using Excel 2010 and higher versions, the PowerPivot solution would be far more efficient.

The steps for creating a pivot table under Solution B for answering both questions above are:

1.  Count of downtime sites.xlsx is saved in a folder on the desktop;
2. Open the workbook, select the data on the Base_Data sheet (including the first row as the header row – it will be range A1:C8741.  Ensure that the header row has some distinctive formatting such as Bold or some colour) and press Ctrl+F3 > New.  In the Name box, type Dummy and click on OK > Close.
3. To cross check that the name assigned above has indeed been assigned correctly, select the data range once again and in the Name box (left of the formula bar), Dummy should appear.
4. Select range A1:C8741 of the Base_Data sheet again and press Ctrl+T to convert this range into a Table.  Ensure that the “My Table has headers” box is checked.  Save the workbook.
5. Open a new worksheet and go to Data > From Other Sources > From Microsoft Query
6. Under Databases, select Excel files > OK
7. In the Directories dialog box, navigate to the folder on the desktop where the workbook file is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and double click on the folder where the workbook is saved.
8. In the left hand side window, select the Count of downtime sites.xlsx file and click on OK
9. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box 10. Click on Next three times
11. Select the option of View Data or Edit Query in Microsoft Query
12. Click on the SQL button, delete the contents in the white space there and paste the following SQL Query

SELECT ucase(dummy.Circle) AS 'Circle', ucase(dummy.Indus_Site_ID) AS 'Indus_site_ID', Count(dummy.Indus_Site_ID) AS 'fault_frequency'
FROM `C:\Users\Ashish\Desktop\Count of downtime sites.xlsx`.dummy dummy
GROUP BY ucase(dummy.Circle), ucase(dummy.Indus_Site_ID)
HAVING (Count(dummy.Indus_Site_ID)>2)

13. Click on OK and on the message box which appears, click on OK
14. Under File, select the last option – Return Data to Microsoft Excel
15. At this stage, if you wish to get data in a tabular form, then select Table.  If you directly want a pivot table, select the second option button – Pivot Table.  For this example, select Pivot Table and in the cell reference box, select any cell where you would like to the result to appear, say cell A1.  Click on OK
16. A counter will run at the bottom left hand side with the title of Reading Data
17. Drag Circle and ID to the to the Row Labels
18. Drag Fault Frequency to the Value Area twice
19. Right click on any one number in the fault frequency column and under Summarise Value by, select Count
20. Right click on any value in the ID column and under Expand/Collapse, select Collapse Entire Field.

Sum highest n numbers based on conditions

{4 Comments}

Assume a two column database with names in column A and numbers in column B.  Names in column A may be repeated.  If a user types a certain name in a cell, a formula should sum the highest three values from column B for that name.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the PowerPivot add-in, then a simple DAX formula can solve this problem.

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system's performance.

You may refer to my solution in this workbook.