Category: POWERPIVOT

Identify buy and sell break points

{0 Comments}

Assume a two column dataset with Date in the first column and Price in the second one.  The purpose is to identify times to buy and sell - buying would be just after the lowest low is confirmed and sell before or just after the highest high is in place. Confirmation is achieved through crossover of moving averages. This data is being used in back testing buy and sell criteria.

Snapshot of base data

Snapshot of expected result

The Lowest Low is the lowest price that occurs before the next Highest High.  The Highest High is the highest price that occurs before the next Lowest Low..  2.77 is the lowest low after the highest high of 3.69 and 3.23 is highest high after the lowest low of 2.77.

You may refer to my solution in this workbook.

Customise Row/Column appearances in Pivot Tables

{0 Comments}

Assume a simple four column dataset with the following columns - User, Month, Leads and Sales.  The dataset shows the user and month wise leads generated and revenue earned.  One may want to analyse this data in a Pivot Table with the User field appearing in the Row labels section, Months field appearing in the Column labels section and the other two Fields appearing in the Value area section.  One can easily create this Pivot Table by dragging field in the quadrant of the Pivot Table Field list pane or in the Pivot Table grid directly.

However, the one customization one may want is to show the Leads generated for all months combined only (not month wise).  The Pivot Table should look as follows:

You may refer to my solution in this workbook.

You may view a video of my solution here

Rank numbers in a range after satisfying conditions

{0 Comments}

Assume a five column dataset - ID, Age, Gender, Time and Class.  For chosen ID's, the objective is to:

1. Assign a Rank (in ascending order of time i.e. lowest time will be rank 1 and so on) to each ID
2. Determine the overall place of each ID - Count of unique time entries lesser than equal to the chosen ID' time entry

These can be computed with the VLOOKUP(), RANK(), FREQUENCY(), INDIRECT() functions and array formulas.  You may refer to range H3:K8 of the Sample worksheet.  So far so good.

What adds to the problem is to meet the objectives outlined above after satisfying additional conditions.  For e.g., one may want to give conditions such as Age between 20 and 35 and colours as Orange and Yellow.  Carrying out computations for ranking and Overall place after satisfying these conditions will make the formulas fairly complex.

I have been able to solve this problem with the help of the PowerPivot.  You may download my solution workbook from this link.

Compute year on year growth in a Pivot Table

{0 Comments}

Assume a three column dataset which has Year, Company ID and Cash flows.  For each Company, there are cash flows for multiple years.  So for Company ID A001, there are 7 rows, one each for 2010 to 2004 and cash flows appearing in a third column.  Let's assume the number of rows are 750,000.

The task is to compute the year on year growth rate in a fourth column.  While this problem can easily be solved by writing a formula in a fourth column, copying that formula all the way down to 750,000 rows will be time consuming and processor intensive.

I have been able to solve this problem using PowerPivot.  You may download the workbook from here.

You may refer to related questions at this link

1. Computing growth % inside a pivot table
2. Compute Pro rata growth rate within a Pivot Table

Compute month wise pending audits

{0 Comments}

Assume a three column dataset showing Audit ID, Date of receipt of audit mandate and Date of audit completion.  There are other columns as well but they are not important for our Analysis.  One may want to compute the following month wise:

1. Which (Audit ID) are the audits pending at the end of every month; and
2. When (Date of receipt of audit mandate) was the mandate for these pending audits received; and
3. Ageing of these pending audits i.e. this would be computed as the last date of the month less Date of receipt of audit mandate

Here's an example:

In January 2014, there are a total of 10 audits reports which were received (Filter "Date of receipt of audit mandate" column on January 2014).  Of these 10 audits, 4 were completed in January 2014 (Filter "Date of audit completion" column on January 2014) itself and therefore there are 6 pending audits.  To this figure of 6, we need to add the audits pending from previous months.  If one filters column "Date of receipt of audit mandate" column on Oct-Dec 2013 and "Date of audit completion" column on dates after January 2014, 8 rows will appear.  This means that there are 8 audits which were received before 1 January 2014 but were completed only after 31 January 2014.  So the total number of pending audits as at 31 January 2014 are 8+6=14.  This task needs to be carried out for all months.

You may refer to my solution in this workbook.

Customise Pivot Table reports

{8 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

{30 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 watch a short video of my solution here

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

{21 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

{6 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