# Category: POWERPIVOT

Assume a table which lists attendees for a Company's Annual day function.  In this Table, data for every attendee is shown on a separate row so if an employee attends the function with his/her spouse and three children, then there will be 5 rows for that employee.

The question is to determine the count of the following family configuration:

1. Employees only (those who attended without spouse and children); and
2. Employees, spouse and children (Family); and
3. Employees and spouse (no children); and
4. Employees and children (no spouse)

You may refer to my solution in this workbook.  I have solved this problem using:

1. MS Excel Formulas based on Set Theory and Venn Diagram; and
2. PowerPivot

Let's say the data sheet has Sales and Profit data by Product, Region, Salesman and Date.  One may want to analyse the following via a Pivot Table

"The Sales Delta by year, month, salesman and product i.e. for May 2012, compute the [Sales in May 2012] less [Sales in May 2011] for every salesman and product.  Do the same for every month for two year (2012 and 2013)."

While this is easy to accomplish using the "Show Value As" feature of a Pivot Table, the problem arises when a particular year is filtered out from the Pivot Table.  This activity of filtering out a year from the Pivot Table causes the other computed Sales Delta figures to vanish.

This problem can be overcome by creating a Pivot Table from the PowerPivot tool of Excel 2010 and higher versions.

You may refer to the question, Pivot Table attempt and PowerPivot solution in this workbook.

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.

A tournament has 18 participating teams with 25 players in each team.  Each team has to play five rounds of the Tournament and not all players play all rounds.  Scores earned by each player in each round are shown in individual cells.  If a player does not play a round, that cell is left empty.

The task is to sum the highest 18 scoring players for each round.  Only the highest 18 players per team count towards the teams score.  If few of the players have the same score at position 18 then only one of them should be included in the overall score.

One solution is to sort each round of scores for each team in descending order and sum the highest 18 values.  This is obviously a time consuming process.

There could be two others ways to solve this problem

Formula driven solution - This uses a spare column, a lengthy formula and the Data > Table functionality.  Since Data > Table is a series of array formula, this solution makes the workbook very sluggish.  You may refer to my solution in this workbook.

PowerPivot solution - This solution is far better than the formula driven one in as much as no spare columns, lengthy formulas or Data > Tables have been used.  The solution in this workbook adds the scores of the highest 18 scoring players per round (If few of the players have the same score at position 18 then all scores are included in the overall score).  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.

Assume a database of Maximum allowed pay and Actual pay for each employee.  The employees have been further categorized into Groups and labour categories.

The task is to create three Pivot Tables (one each with Employee name, Group and Labour category in the row labels) with the following information in the Value area section:

1. Actual salary
2. Maximum permissible salary
3. OverUnderMax - This is calculated as the difference between Actual Salary and Maximum permissible salary
4. Penalty - Maximum of 0 and OverUnderMax

As can be seen in the Pivot Table worksheets of this workbook, there is a problem with the result of the calculated field formula result in the Grand Total cell.

You may refer to my solution in the PowerPivot worksheets.  Please note that to see my result in the PowerPivot worksheets, you will have to use the PowerPivot add-in for MS Excel 2010/2013.

Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all.

In this workbook, I have shown the technique to count unique values with conditions on a large database

1. Using PowerPivot - Will only work in Excel 2010 and higher versions

2. Using a  normal Pivot Table and SUMPRODUCT() function - Will work for all versions but is not as efficient as the PowerPivot solution.

To count unique values with conditions on small databases, you may refer to the following link

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.

Assume a four column input data range (refer sheet named "Input" of this workbook) arranged as follows:

1. First tow columns are Group and Type which have text values
2. Third column is a month column with entries such as Dec_11 and Sep_12.  These denote 12 months ended December 2011 and 9 months ended 2012
3. Fourth column is Revenue which has numbers

The objective is to compute pro rata growth rate of Sep_12 revenue over Dec_11 revenue within a Pivot Table.  In the workbook (see link above), I tried to use a calculated item formula within a Pivot Table to compute this but encountered three difficulties in doing so (refer Notes section of sheet named "Result of Calc item formula ").

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 are:

The Objective is to change the orientation of the base data such that a calculated field formula can be written within a Pivot table instead of a calculated item formula.  The process to change the orientation of the base data is described below:

1. Select range A2:D14 of Input sheet and press Ctrl+F3 > New.  In the name box, type Dummy
2. Select range A2:D14 of Input sheet and press Ctrl+T to convert to a Table
3. Open a third worksheet in the workbook (downloaded from the link above) and save the workbook, say on any folder on the desktop
4. While the active cell is any cell of the new worksheet, go to Data > From Other Sources > From Microsoft Query
5. Under Databases, select Excel files > OK
6. In the Directories dialog box, navigate to the folder on the desktop where the workbook is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and click on the folder where the workbook is saved
7. In the left hand side window, select the workbook and click on OK
8. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box
9. Click on Next three times
10. Select the option of View Data or Edit Query in Microsoft Query
11. Click on the SQL button and delete whatever you see in the box there
12. Enter the following SQL statement in the white box

Transform sum(rev)
SELECT Dummy.`GROUP`, Dummy.TYPE
FROM Dummy
Group by Dummy.`GROUP`, Dummy.TYPE
Pivot Mth

13. When you click on OK, you will see a four column database (refer sheet named "Result of MS Query")
14. Under File, select the last option – Return Data to Microsoft Excel
15. At this stage, if you wish to get data as you saw in MS Query then select Table.  If you directly want a pivot table, select the second option button – Pivot Table.  Select any cell where you would like to the result to appear, say cell A1. 16. Click on OK.  A counter will run at the bottom left hand side with the title of Reading Data
17. If you had selected pivot table in step 15 above, then the pivot table grid/layout will appear
18. You may now drag fields to create a pivot table
19. You may now write a calculated field formula within the Pivot Table (refer sheet named "Output")

As can be observed in the "Output" sheet, all the shortcomings mentioned in the Notes section of the "Result of Calc item formula" sheet have been overcome.

Assume the following sentences in range B2:B6

B2 - This is an Apple Pie
B3 - An apple a day keeps a doctor away
B4 - These Pears, apples and mangoes are sweet
B5 - In this season, prices of mangoes have increased
B6 - This is a glass of Guava juice

In range B9:B10, type Mango and Apple.  The objective is to count the sentences in range B2:B6 which exclude the words mentioned in range B9:B10.

The array formula (Ctrl+Shift+Enter) for getting the count of exclusions would be:

=SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT(\$B\$9:\$B\$10))),1*(ISERROR(SEARCH(\$B\$9:\$B\$10,TRANSPOSE(\$B2:\$B\$6)))))=COUNTA(\$B\$9:\$B\$10)))

The result should be 1.

The array formula (Ctrl+Shift+Enter) for getting the count of inclusions (sentences which include these words), would be:

=COUNTA(\$B\$2:\$B\$6)-SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT(\$B\$9:\$B\$10))),1*(ISERROR(SEARCH(\$B\$9:\$B\$10,TRANSPOSE(\$B2:\$B\$6)))))=COUNTA(\$B\$9:\$B\$10)))

The result should be 4.

One can also solve the problem with the PowerPivot tool.  You may refer to my PowerPivot solution in this workbook.

Having created a Pivot Table from a Base_Data sheet, one may want to compute the Account wise and Growth % of 2010 over 2009.

There are two ways one can go about answering the question 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 are:

1. Select cell C4 on the sheet "Pivot Table"
2. Go to Pivot Table Tools > Options > Fields, Items and Sets > Calculate Item.  In Excel 2007, this will be Pivot Table Tools > Options > Formulas > Calculated Item
3. In the Name box, type Growth
4. In the Formula bar, enter =('2010' /'2009' )-1 and press OK
5. Select the Growth column in the pivot table and press Ctrl+Shift+5 to convert the decimals to percentages

The problem with the above process is that the Growth % in the Grand Total column (cell D10) will be incorrect i.e. -31.93%.  Unfortunately, this is the simple summation of the Growth % above.  Ideally, this should be 1.77%and it should be computed as:

=(323,332-317,718)/317,718

First of all, this problem is happening because cell D10 is at the intersection of two formulas - one is Excel's default SUM function for the Grand Total column and the other is the calculated field formula created above.  We need to prioritise the application of formula on this particular cell i.e. the Growth % formula written above should take precedence over the default SUM function.  To accomplish this, try the following steps:

1. Right click on cell A10 (Grand Total) and select Remove Grand Total
2. Select cell A9 and go to Pivot Table Tools > Options > Fields, Items and Sets > Calculate Item.  In Excel 2007, this will be Pivot Table Tools > Options > Formulas > Calculated Item
3. In the Name box, type Total
4. In the Formula bar, enter =Ret Sales + Met Sales + Det Sales + Fet Sales + Get Sales.  You can do so by double clicking on the individual items available
5. After performing the steps above, the problem would be the same as we had earlier i.e. the Growth % in cell D10 will show -31.93%
6. To prioritise calculations in cell D10, try this:
a. Select any one cell in the pivot table and go to Pivot Table Tools > Options > Fields, Items and Sets > Solve Order.  In Excel 2007, this will be Pivot Table Tools > Options > Formulas > Solve Order
b. Select the first item and click on Move Down
c. Click on Close

The Growth % in cell D10 should now appear as 1.77%.

Change in growth from -31.93% to 1.77%!!!!!!