Customise Pivot Table reports

{0 Comments}

Assume that someone has created a Pivot Table using the PowerPivot tool.  Now one may want to customize the Pivot Table even further by:

1. Shuffling rows in the Pivot Table; and
2. Recomputing subtotals and Grand Totals after reshuffling rows

A Pivot Table created via the PowerPivot tool can be converted into a normal range via CUBE formulas.  Once each cell carried an individual formula, one can very easily perform the two tasks mentioned above.  You may view my solution in this workbook.

You may watch a short video of my solution here

Consider a Pivot Table Value field column as a criteria for computing another Value Field column

{8 Comments}

Assume a simple three column dataset showing hours worked by different machine on different dates.  So column A is Date, column B is Machine Name and column C is hours worked.  There are duplicates appearing in column A and B .  Blanks in column C depict machine idle time.

The task is to create a simple three column dataset showing all unique Machine names in the first column, Last day on which the machine worked in the second column and hours worked on the last day in the third column.

This problem can be solved by using formulas (Refer first worksheet of the workbook) but if one has to use a Pivot Table, then there would be a few problems.

1. The Grand Total for the Date Field should be blank because on cannot determine the Last day on which the machine worked across different machine types.  A conventional Pivot Table shows the Maximum of all dates appearing in the Date Field.

2. The Grand Total for the Hours worked Field should be a summation of the total hours worked on last day across all machine types.  A conventional Pivot Table shows the Maximum of all hours worked appearing in the Hours worked Field.

3. The biggest problem of them all is that there is no way to give a criteria as the Last day for that machine for computing another Field in the Pivot Table.  Please refer the file for a better understanding.

This problem can be solved using the PowerPivot.  You may refer to my solution in this workbook.

Sales data modelling and interactive visualisations

{0 Comments}

Visualise Sales Data of a Non-Alcoholic Beverage Company with basic columnar information such as Date of Sale, Time of Sale, Brand, Stock Keeping Unit (SKU), State, City, Quantity sold, Unit Price and Salesman Code.  In this sales dataset, each line item represents one visit for one SKU.  If nothing is sold in a certain visit, then the SKU column displays No Sale.  So effectively there is a line item for each visit whether or not something is sold in that visit.

From this simple Sales dataset, here are a few questions which one may need to find answers to:

1. How did the Company perform (in both years 2013 and 2014) on two of the most critical Key Performance Indicators (KPI's) - Quantity sold and Number of Visits.  Also, what is the month wise break up of these two KPI's.

2. Study and slice the two KPI's from various perspectives such as "Type of Outlet visited", "Type of Visit" - Scheduled or Unscheduled, "Day of week", "Brand", "Sub brand".

3. Over a period of time, how did various SKU's fair on the twin planks of "Effort" i.e. Number of visits YTD and "Business Generated" i.e. Quantity sold YTD.

4. Analyse the performance of the Company on both KPI's:
a. During Festive season/Promotional periods/Events; and
b. During different months of the same year; and
c. During same month of different years; and
d. Quarter to Date

5. "Complimentary Product sold Analysis" - Analysis displayed on online retailers such as Amazon.com - "Customers who bought this also bought this".  So in the Sales dataset referred to above, one may want to know "In this month, outlets which bought this SKU, also bought this much quantity of these other SKU's."

6. "Outlet Rank slippage" - Which are the Top 10 Outlets in 2013 and what rank did they maintain in 2014.  What is the proportion of quantity sold by each of the Top 10 outlets of 2013 to:
a. Total quantity sold by all Top 10 outlets in 2013; and
b. Total quantity sold by all outlets in 2013

7. In any selected month, which new outlets did the Company forge partnerships with

8. Which employees visited their assigned outlets once in two or three weeks instead of visiting them once every week (as required by Management).

9. Which outlets were not visited at all in a particular month

10. Business generated from loyal Customers - Loyal Customers are those who transacted with the Company in a chosen month and in the previous 2 months.

These are only a few of my favourite questions which I needed answers to when I first reviewed this Sales Data.  Using Microsoft Excel's Business Intelligence Tools (Power Query, PowerPivot and Power View), I could answer all questions stated above and a lot more.

You may download the workbooks from here

1. Sales data for only 1 City; and
2. Sales data for multiple cites

The Analysis on both workbooks is the same - the only difference is that the number of rows in the second workbook are far more.

You may watch a short video of my solution here

Display data from the Grand Total column of a Pivot Table on a Stacked Pivot Chart

{0 Comments}

Assume a simple Sales dataset from which a Pivot Table has been created.  The Pivot Table has been sliced by two columns of the dataset.  To represent data graphically, a Stacked Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook).  The Stacked Pivot Chart has Months on the X-axis and each month has stacks for various products sold in that month.  By design, a Pivot Chart never displays data from the Grand Total column of a Pivot Table.  The Select Data button the Pivot Chart Tools button does not allow the user to reselect the Source data to include the Grand Total column.  The only option left in this case is to copy the Pivot Table and paste it as Paste Special > Values in another range and then create a Normal Stacked chart from this Table.  But in doing so, any change in the slicer or Base data will not have any effect on the Stacked Chart because the source of the Stacked Chart is a static range.

This problem can be overcome by using the PowerPivot tool and CUBE functions (available in Excel 2007 + versions).  You may download the solution workbook from here.

You may watch a short video of my solution here

Show Slicer selection on a Graph

{0 Comments}

Assume a simple Sales dataset from which a Pivot Table has been created.  The Pivot Table has been sliced by two columns of the dataset.  To represent data pictorially, a Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook).  Now let's say, a user makes a few slicer selections on the Pivot Table worksheet.  When one now clicks on the Pivot Chart worksheet, one does not see what selections were made in the slicers (which are placed on the Pivot Table worksheet).  So one has to go back and forth between the two worksheets to keep track of the slicer selections made.  One may want to view the slicer selections made on the Pivot Chart as well.  Changes made in the slicer selections should automatically reflect on the Pivot Chart worksheet.

This can be accomplished by using the PowerPivot tool and CUBE functions (available in Excel 2007 + versions).  You may download the solution workbook from here.

You may watch a short video of my solution here

Align data from two columns

{0 Comments}

Assume two data lists. List1 has Test1,Test2 and Test3 in A2:A4 and List2 has Test1,Test4,Test2 in B2:B4. I would like to align data from both lists to appear as follows:

List1    List2
Test1   Test1
Test2   Test2
Test3
Test4

You may refer to my solution in this workbook.

You may watch a short video of my solution here

Ignore errors while adding non contiguous cells of a range

{7 Comments}

In range E5:E13, assume the entries are

1
2
#DIV/0!
4
5
6
#N/A
8
9

In cell I9, one may want to sum up the figures from cells E5,E7,E11 and E13. If one supplies the individual cell references to the SUM function, then the result will be a #DIV/0! error because the SUM function is intolerant towards errors supplied in its range. The formula which will resolve this problem is:

=SUMPRODUCT(SUMIF(INDIRECT(A11:A14),">-1E100"))

A11:A14 has E5,E7,E11 and E13.

Converting a tabular data layout to a matrix layout

{5 Comments}

Visualise a dataset from where you want to create different columns for entries which are listed down in one single column. In a scenario where the entries which have to be shown under each of these columns so created are numeric, a simple Pivot Table will sufficed. However, if entries to be shown under the columns are text entries, then a Pivot Table will not work because in the Value area section of a Pivot Table, only numbers can be displayed. If a text column is dragged to the Value area section of a Pivot Table, then a count is displayed.

To create a multi column range from an existing dataset, you may refer to my solution in this workbook.

You may watch a short video of my solution here

Merge and append data from two worksheets

{2 Comments}

Visualise a workbook with two worksheets. The tab name of the first worksheet is "My Table" and of the other worksheet is "My colleagues Table". Each worksheet has 4 columns - First Name, Last Name, Pin code and Mobile Number. In "My Table", the first, second and third columns are filled up and in "My colleagues table", first, second and fourth columns are filled up. Furthermore, there are a few additional names on "My colleagues worksheet". The task is to create a combined database from both these worksheets and for doing so, the following sub tasks have to be performed:

1. Bring over data from the 4th column of "My colleagues Table" to the 4th column of "My Table" (Merge data); and
2. Bring over the additional rows from "My colleagues Table" to "My Table" (Append data)

If you are using the Power Query add-in, then a few simple steps (no formulas) can solve this problem. The result will be dynamic and refreshable (just as in a Pivot Table).

You may download the workbook from here

You may watch a short video of my solution here

Recompute figures in the Value area section of a Pivot Table after receiving a user input

{0 Comments}

Assume a simple 5 column database with the following data

1. Circle Name - A text field
2. PO_Number - An is an alphanumeric field
3. Quantity sold - A numeric field
4. Unit Price - A numeric field denominated in US$
5. Revenue - A numeric field which is computed as Quantity sold * Unit Price

To determine the Circle and PO Number wise Quantity sold and Revenue, one can drag the first two fields to the Row labels and drag the third & fifth fields to the Value area section of a Pivot Table.

Now one may want to additionally view the Revenue in one of the following additional currencies - GBP and EUR.  So once the user selects, say GBP, he should additionally be able to choose from one among three cases - Base, Optimistic and Pessimistic.  Once the case is selected, the additional column so created should multiply the Revenue (in US$) with the exchange rate for the selected case.

I can think of three ways to workaround this issue:

1. Create additional columns in the Base data sheet - So there will be 6 additional columns that will get created in the Base data sheet

a. GBP - Base
b. GBP - Optimistic
c. GBP - Pessimistic
d. EUR - Base
e. EUR - Optimistic
f.  EUR - Pessimistic

Once these columns are created, one can readjust the Pivot Table source data range (to make these additional 6 columns appear in the Pivot Table Field List) and then check the columns which need to be viewed in the Pivot Table.  The challenge with implementing this method is that for a large dataset, say 50,000 rows, 300,000 cells with formulas (albeit simple multiplications) will need to be used.  This will add to file size.

2. Write calculated Field formulas in the Pivot Table itself - One can write 6 calculated Field formulas - one for each currency - case combination and then drag the desired fields in the Pivot Table.  The challenge with implementing this method is that if one wants to edit the exchange rate, then one will have to edit the calculated field formula (This is because, in calculated field formulas, one cannot refer to cells/ranges/named ranges) which is not really that intuitive/straight forward.

3. Use Power Pivot to fetch exchange rates from cells and allow the user to select Currencies and Cases via slicers - With the help of simple Power Pivot DAX formulas and slicers, one can resolve both problems mentioned above.

You may refer to my solution in this workbook (this solution is only for those using the PowerPivot tool).