The objective is to create a simple matrix with months in the row labels, years in the column labels and sales figures in the value area section. The twist in the question is that for years prior to the current year (2018 in this dataset), sales should only appear till the month for which there is data for the current year. For e.g., for 2018, data is only till Month 4 and therefore for prior years as well, data should only appear till Month 4. As and when Sales data gets added below row 17, data for prior years should also go up to that month.

The expected result is

You may download my PBI file from here. The same solution can be obtained in Excel as well (using Power Query and PowerPivot).

Here's a simple dataset showing the Date of sale, Customer Name and Sales amount.

Date

Customer Name

Sales amount

12-03-2017

A

1

12-03-2017

A

2

12-03-2017

A

3

12-03-2017

B

4

12-03-2017

B

5

12-03-2017

B

6

12-03-2017

B

7

12-03-2017

B

8

13-03-2017

A

1

13-03-2017

A

1

The objective is to determine Customer wise:

Highest revenue; and

Date on which that highest revenue was earned

The expected result is

Row Labels

Highest revenue

Date of highest revenue

A

6

12-Mar-2017

B

30

12-Mar-2017

Grand Total

36

So, for A, revenue earned on March 12, 2017 is 6 and on March 13, 2017 is 2. Since the higher of the two is 6, that is the result which should appear in the Pivot Table.

I have solved this problem with the PowerPivot. You may download my solution workbook from here.

Assume a dataset with two columns which lists down the student names in column A and courses opted for in column B. Since one student can opt for multiple courses and the same course can be taken up by multiple students, there can be repetitions in both columns. The objective is to create a matrix like data structure (with courses appearing in both row and column labels) with numbers inside the matrix quantifying the "Number of students who opted for course A and C". So, for all possible course combinations, one may want to know the number of students who opted for those combinations.

The description above can be extended to cases where buying behavior has to be analysed. A sore manager may want to know "How many people who buy Brand A also buy Brand B."

Here's a snapshot of the source data and expected result

The number 1 in cell H4 (and cell F6) means that there is only one student who opted for courses B and D. Likewise, 3 in cell H5 (and cell G6) means that 3 students opted for courses C and D.

You may refer to my Power Query and PowerPivot solution in this workbook. Power Query has been used for generating a dynamic list of Courses and Power Pivot has been used for writing the DAX formula for quantifying within the matrix.

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.

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.

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.

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.

Employee headcount – a multi column dataset with information such as Employee code, Date of Joining, Age, Division, Department and Location. Each row represents data for one employee. The number of rows on this worksheet is approximately 700.

Training Data - a multi column dataset with information such as Employee code, Training Date from, Training Date to, Training Program Name, Training Program Category (Internal and External), Training Location and Training Service Provider. Each row represents one training attended by one employee. The number of rows on this worksheet is approximately 2,600.

Let’s suppose that the training calendar of this company runs from July to June. Some questions (only few mentioned for illustration purposes) which a Training Manager may need answers to are:

1) How may unique employees were trained each year; and
a) Of the unique employees trained, how many were first time trainees and how many were repeat trainees
i) Of the first time trainees:
(1) How many joined this year
(2) How many joined in past years
ii) Of the first time trainees:
(1) How many were trained within the first year of joining
(2) How many were trained in the second year of joining
(3) How many were trained in the third year of joining
(4) How many were trained after three years of joining
iii) Of the repeat trainees:
(1) What is the average gap (in days) between trainings
(2) What is the minimum gap (in days) between trainings
(3) What is the maximum gap (in days) between trainings

Getting answers to the questions mentioned above would entail writing a lot of lookup related formulas, applying filters, copying and pasting and then creating Pivot Tables. While the example taken above is that of a training database, you may envision “drilling down to and slicing” any dataset – Marketing, Sales, Purchase etc.

You may watch a short video of my solution here

In these two workbooks, you will be able to see the level to which one can drill down and analyse data using the Power Pivot add-in. When you open this workbook, please go the first worksheet and make the relevant choice of MS Excel version first so that you start looking at the Analysis from the correct worksheet.

You will be able to see the analysis in these workbooks only if you are using one of the following versions of MS Office:

1. Excel 2013 Professional Plus; or
2. Excel 2010 with the Power Pivot add-in installed. Power Pivot is a free add-in from Microsoft which can be downloaded from here.

Lastly, if you are using the Power Pivot add-in in Excel 2010, you will not be able to see the underlying Data Model or the calculated Field formulas because this workbook has been created in Excel 2013 Professional Plus and unfortunately the Power Pivot model is not backward compatible. However, all the analysis performed in this workbook can be performed in Excel 2010 as well (with the Power Pivot add-in installed).

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.

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.