Category: POWERPIVOT

Distribute projected revenue annually

{2 Comments}

Here is a dataset showing Project wise forecast of open opportunities.

  1. Topic is the Project Name
  2. Est. Close Date is the date by when the opportunity would be closed i.e. the project would be won from that Client
  3. Duration is the time (in months) for which the project would run
  4. Amount is the total amount that would be billed for that project

Clients are invoiced annually only. So in the example below:

  1. Project ABC is for US$1 million with a duration of 24 months and is expected to be closed in Oct. 2017.  We need to model the data to show the billing every 12 months.  So for ABC US$500K would be billed in Oct-2017 and another US$500K in Oct-2018.
  2. Project GEF is for US$2 million with a duration of 18 months and is expected to be closed in Feb. 2018. We need to model the data to show US$1.3 million in Feb-2018 and another US$666K in Feb-2019.  The monthly billing is US$2 million divided by 18 and then multiplied by 12 - this amounts to US$1.3 million.
Topic Est. Close Date Duration (Months) Amount
ABC 01-10-2017 24 1,000,000
GEF 01-02-2018 18 2,000,000
XYZ 01-03-2018 30 1,000,000

The expected result should look like this:

Row Labels Oct-17 Feb-18 Mar-18 Oct-18 Feb-19 Mar-19 Mar-20 Total
ABC 500,000 500,000 1,000,000
GEF 1,333,333 666,667 2,000,000
XYZ 400,000 400,000 200,000 1,000,000
Grand Total 500,000 1,333,333 400,000 500,000 666,667 400,000 200,000 4,000,000

I have solved this problem using Power Query and PowerPivot. You may download my solution workbook from here.

Alter the behaviour of a filter/slicer from OR to AND

{3 Comments}

Here is a simple two column dataset which shows EmpID in column A and DateWorked in column B.  My objective is to filter the table to show those employees who worked ONLY on August 17 and August 18.  I'd like to exclude employees who:

  1. Did not work on both these days; and
  2. Worked on both these dates but also worked on other dates

A simple filter on the DatesWorked column with the criteria of August 17 and August 18, would return 7 employees.  This is clearly wrong because this will show employees who:

  1. Worked on any of the two chosen dates; and
  2. Worked on dates other than the two chosen dates

From Table 1, one can clearly observe that the correct result should be EmpID E.  Only EmpID E worked on August 17 and 18.  While others also worked on both these dates, they worked on other dates as well and should therefore be excluded.

The problem outlined above holds true for selections made via a slicer in a Pivot Table as well.  So this is a generic problem with a filter/slicer - conditions specified therein behave like OR conditions, not like AND conditions.

Base dataset

EmpID DateWorked
A 14-08-2017
A 15-08-2017
A 16-08-2017
A 17-08-2017
A 18-08-2017
B 15-08-2017
B 16-08-2017
B 18-08-2017
C 14-08-2017
C 15-08-2017
C 16-08-2017
C 17-08-2017
C 18-08-2017
D 14-08-2017
D 15-08-2017
D 16-08-2017
D 17-08-2017
D 18-08-2017
E 17-08-2017
E 18-08-2017
F 16-08-2017
F 17-08-2017
G 18-08-2017

Filtered dataset (after specifying the criteria as 17 Aug and 18 Aug)

EmpID DateWorked
A 17-08-2017
A 18-08-2017
B 18-08-2017
C 17-08-2017
C 18-08-2017
D 17-08-2017
D 18-08-2017
E 17-08-2017
E 18-08-2017
F 17-08-2017
G 18-08-2017

So the question is "How does one change the behavior of a filter/slicer from OR to AND?". To make things worse, the data is arranged as follows:

EmpID DateWorked
A 14/8/17;15/8/17;16/8/17;17/8/17;18/8/17
B 15/8/17;16/8/17;18/8/17
C 14/8/17;15/8/17;16/8/17;17/8/17;18/8/17
D 14/8/17;15/8/17;16/8/17;17/8/17;18/8/17
E 17/8/17;18/8/17
F 16/8/17;17/8/17
G 18-08-17

As you can see, there are multiple dates in a single cell separated by a ;.

You may refer to my Power Query and PowerPivot solution here.

Merge data from 2 data sources in a Pivot Table to get a Consolidated Project view

{0 Comments}

Here's a simple four column table showing date wise amount spent per project

Date Project ID Project Name Total Amount
10-01-2015 P250 Project A 100
15-03-2015 P250 Project A 250
01-08-2015 P250 Project A 175
01-12-2015 P250 Project A 90
16-01-2016 P250 Project A 75
28-02-2016 P250 Project A 105
23-02-2016 P300 Project B 175
01-03-2016 P300 Project B 85
06-03-2016 P300 Project B 66
07-04-2016 P300 Project B 150
15-03-2016 P300 Project B 25
28-03-2016 P300 Project B 200
26-04-2016 P300 Project B 450

Here's another table which shows project wise gates assigned within a time frame.

Project ID Project Name Project Gates Beginning date Ending date
P250 Project A Release Gate 01-01-2015 31-05-2015
P250 Project A Mid Gate 01-06-2015 30-12-2015
P250 Project A Review Gate 31-12-2015 31-01-2016
P250 Project A Final Gate 01-02-2016 29-02-2016
P300 Project B Release Gate 15-02-2016 29-02-2016
P300 Project B Mid Gate 01-03-2016 14-03-2016
P300 Project B Review Gate 15-03-2016 14-04-2016
P300 Project B Final Gate 15-04-2016 31-12-2016

The result expected is a Pivot Table which should mesh data from both datasets:

Project ID Project Name Date Gates Project Amount
P250 Project A 10-01-2015 Release Gate 100.00
15-03-2015 Release Gate 250.00
01-08-2015 Mid Gate 175.00
01-12-2015 Mid Gate 90.00
16-01-2016 Review Gate 75.00
28-02-2016 Final Gate 105.00
P300 Project B 23-02-2016 Release Gate 175.00
01-03-2016 Mid Gate 85.00
06-03-2016 Mid Gate 66.00
15-03-2016 Review Gate 25.00
28-03-2016 Review Gate 200.00
07-04-2016 Review Gate 150.00
26-04-2016 Final Gate 450.00
Grand Total       1,946.00

While the 1st, 2nd, 3rd and 5th columns are easy to get in a Pivot Table from Table 1, the challenge is to get the "Gates" information from Table 2 inside the Pivot Table.

You may refer to my Power Query a.k.a Get & Transform (available under Data) in Excel 2016 and PowerPivot solution here.

Compute standard hours spent on weekdays by Tier, Week, Month and Country

{0 Comments}

Imagine a Sales dataset with the following columns - ID, Country, Start date, End date and Tier. Here's a snapshot of the table:

ID Country Start date End date Tier
33948 ES 25-Sep-17 28-Sep-17 3
19820 US 08-Oct-17 17-Oct-17 4
9118 US 27-Oct-17 03-Nov-17 2
1563 ES 02-Sep-17 07-Sep-17 2
11087 US 18-Oct-17 27-Oct-17 1
15057 US 05-Nov-17 13-Nov-17 4
13567 ES 18-Oct-17 26-Oct-17 4
22362 DE 28-Aug-17 30-Aug-17 4
31301 FR 04-Nov-17 10-Nov-17 2
22838 US 05-Sep-17 13-Sep-17 4

There is another 2 column table which lists down the Tier wise standard hours. Here's a snapshot of the table:

Tier Daily load hh
1 0.7
2 0.72
3 0.8
4 1.2

The result which one expects from these two tables is shown below:

Tier
Month Week 1 2 3 4 Grand total   Slicer
9 35 x x x x x US
36 x x x x x UK
37 x x x x x FR
38 x x x x x IT
39 x x x x x ES
Grand total   x x x x x    

Let's take a specific example to show the result expected for the US Region. From the last row of the Sales Table, one can see that there is a ID 22838 in US which spans the date range 05-Sept-17 to 13-Sep-17. These dates span week numbers 36 (from 05-Sept-17 to 08-Sep-17) and 37 (from 11-Sept-17 to 13-Sep-17) [The week numbers have been obtained by using Excel's WEEKNUM() function]. So, is US, for Tier 4, in Week 36, the standard hours worked on weekdays would be 4.8 (4 days * 1.2 hours per day). Likewise, in week 37, the standard hours worked on weekdays would be 3.6 (3 days * 1.2 hours).

I have solved this problem using Power Query a.k.a Get & Transform (Available under Data) in Excel 2016 and PowerPivot.  You may download my solution workbook from here.

Determine cumulative interest payable on an annuity with varying time periods

{0 Comments}

Imagine a fixed monthly amount due to an Organisation for services rendered to various customers.  While an invoice is raised every month by this Organisation, not all pay up the dues on time.  For unpaid dues, the Organisation charges its client interest ranging from 3% to 9% per annum.  The objective is to determine cumulative interest payable by various customers to Organisation X.

The base data looks like this

Client Monthly revenue Int. calculation start date Int. calculation end date Interest rate
Client A 33,967 01-Aug-16 25-Jul-17 9.00%
Client B 123 12-Sep-16 30-Nov-17 4.00%

Given the dataset above, the total interest payable by Client A is Rs. 16,237.20.  The calculation is shown below:

From To Days for which interest should be paid Principal Interest
02-Aug-16 31-Aug-16 328.00 33,967.00 2,745.26
01-Sep-16 30-Sep-16 298.00 33,967.00 2,494.17
01-Oct-16 31-Oct-16 267.00 33,967.00 2,234.71
01-Nov-16 30-Nov-16 237.00 33,967.00 1,983.62
01-Dec-16 31-Dec-16 206.00 33,967.00 1,724.16
01-Jan-17 31-Jan-17 175.00 33,967.00 1,464.70
01-Feb-17 28-Feb-17 147.00 33,967.00 1,230.34
01-Mar-17 31-Mar-17 116.00 33,967.00 970.88
01-Apr-17 30-Apr-17 86.00 33,967.00 719.79
01-May-17 31-May-17 55.00 33,967.00 460.33
01-Jun-17 30-Jun-17 25.00 33,967.00 209.24
01-Jul-17 25-Jul-17 - 33,967.00 -
Total       16,237.20

You may download my solution workbook with from here. I have solved this problem using normal Excel formulas and the PowerPivot.

Compute an average for the same day in the past 3 years

{4 Comments}

Assume a simple two column dataset with dates in column A and numbers in column B. The dates in column A are from January 1, 2013 to December 31, 2016 and numbers in column B are for the period January 1, 2013 to December 31, 2015 (there are no numbers for January 1, 2016 to December 31, 2016).

The objective is to "Compute an average for each day of calendar year 2016. The average should be for the occurrence of that day in the previous 3 years". Here's an example:

1. January 1, 2016 was a Friday (the first Friday of 2016) and is in cell A1097
2. In cell B1097, the average should be computed as: Average of the "First Friday of each of the previous 3 years"
3. January 8, 2016 was a Friday (the second Friday of 2016) and is in cell A1104
4. In cell B1104, the average should be computed as: Average of the "Second Friday of each of the previous 3 years"

I have solved this problem with the help of the PowerPivot. You may refer to my solution in this workbook.

Sales data modelling and interactive visualisations of an E-Commerce Company

{8 Comments}

In this workbook, I have Sales data of an E-Commerce Company for 3 months.  The typical columns in the base data are:

1. Order Date/Time
2. City to which orders were shipped
3. Order Number
4. Payment Type i.e. Cash on delivery, Net Banking, EMI's
5. Order Status i.e. Delivered or cancelled
6. SKU's which the ordered items fall into
7. Products which the ordered SKU's fall into
8. Categories which the ordered products fall into

Given this simple tabular representation, one may want to analyse and visualize this dataset from multiple perspectives based on user selections, such as

"What was the revenue earned from the Top 5 products in the A100 category in April for orders shipped to New Delhi?"

In this query framed above, the end user should have the leeway to select any/all of the underlined facets.  So one can either choose revenue earned or Number of orders.  Likewise, one can either select Top 5 products or Top 15 products/Top 5 SKU's etc.

With relative ease, one should also be able to "Perform an affiliate analysis" showing which categories are ordered together (to study affiliations).  Please review this post for an independent discussion on "Affinity Analysis".

Furthermore, one should be able to perform a free form timeline search such as  - "I would like to study growth in Total revenue of March 2-8 2015 over Feb 1-4 2015"

You may download the workbook from the link shared above.

You may watch similar videos showcasing the capabilities of Business Intelligence in MS Excel:

1. Analyse Sales data of a Beverage Company
2.
Analyse Training data of a Company

Here's a video showing the capabilities of this Sales data model

You may also watch this short video to see how I visualized the revenue flow from Categories to Shipping cities during different Order periods using Custom visuals available in PowerBI desktop.

Please feel free to download the PowerBI desktop workbook of the video shown above from here.

For a detailed overview of Sankey diagrams (a Custom visual available in PowerBI desktop), you may refer to my Blog article here.

Another great Custom visual (Sand Dance) which allows data discovery has been shown at this link.  At that link, you will also be able to see how I queried the underlying dataset using "Natural Language".

Workaround to the problem of creating a Pivot chart after using “% of row total” calculation in a Pivot Table

{0 Comments}

Here is a dashboard created with a Pivot Table, a Pivot chart and slicers (Click to enlarge image).  In the Pivot Table, the % have been computed using "% of row total".

Untitled

The Pivot chart shows two columns per month - one for complete and the other for incomplete.  The objective is to show only the Complete column per month in the Pivot chart.  If one filters the Pivot Table only on Complete, then the Pivot chart shows an unexpected result - each column will go upto 100%.  This happens because all calculations in a Pivot Table happen only on the visible cells.  Once the Incomplete column is hidden, the "% of row total" calculation will return 100% which in turn reflects in the Pivot chart.

I have resolved this problem with the help of the PowerPivot.  You may refer to my solution in this workbook.

Perform an “Affinity analysis” to identify co-selling products

{2 Comments}

Affinity analysis encompasses a broad set of Analytic techniques aimed at uncovering the associations and connections between specific objects: these might be visitors to a website (customers or audience), products in a store or content items on a media site. Of these, “market basket analysis” is perhaps the most common example. In a market basket analysis, one analyses combinations of products that frequently co-occur in transactions.  For e.g., Of all the shoppers today, how many purchased a school uniform and a school bag.  A retailer can use this information to:

1. Improve the customer shopping experience by rearranging the store layout (place products that co-occur together close to one another); and
2. Run a focused marketing campaign (e.g. target customers who buy a school uniform with offers on school bags, to encourage them to spend more on their shopping basket)

Online retailers and publishers can use this type of analysis to:

1. Inform the placement of content items on their media sites, or products in their catalogue
2. Drive recommendation engines (like Amazon’s customers who bought this product also bought these products…)
3. Deliver targeted marketing (e.g. emailing customers who bought products specific products with other products and offers on those products that are likely to be interesting to them)

Consider a dataset with four columns - Date, Order Numbers, Items sold and Item Description.  To simplify, let's ignore columns 1 and 4 for now.  One may want to know the answer to the following question:

For a particular item sold (say Item X), in how many orders (order frequency) were items X and Item Y were sold together OR in how many orders (order frequency) were items X and Item Z together.

In the Excel workbook (download link below), number of rows in the Sales data sheet is 265,321 (file size is 11 MB) and the number of unique items sold is 23,201.  In Excel, one has to pair each of the 23,201 items with the other 23,200 items to know if there is any affiliation or not.  The sheer size of that dataset would make any formula unusable here.

I have solved this problem with the help of the PowerPivot.  Please note that in the PowerPivot solution worksheet, I have filtered the Row labels on a specific Item number.  This is so because if I attempt to clear the filter criteria to view all Item numbers, then I get a message saying that the number of rows exceeds 1 million.  Also, since the calculations are very intensive, recalculation takes time when you change the criteria in the filter dropdown of the Pivot Table.  Therefore, once you change the filter criteria in the Pivot Table, wait for the Reading data counter (bottom right on the taskbar) to finish processing.

Interpretation

1. Cell C5 - 1,725 orders contained the Item number TNB521711234E.  This figure can be verified via the following steps:
a. On the Sales data worksheet, filter the item number column on TNB521711234E.
b. Copy the resulting rows of the Order number column to another worksheet
c. Select the data so copied and go to Data > Remove Duplicates
d. Count the ensuing rows
2. Cell C6 - Of the 1725 orders containing Item number TNB521711234E, 540 contained Item number TNB52C1
3. Cell C7 - Of the 1725 orders containing Item number TNB521711234E, 446 contained Item number EMT34

You may refer to my solution in this workbook.

Filter the Rank Field in a Pivot Table

{4 Comments}

Imagine a two column dataset - Customer Code and Material Number (with alphanumeric data).  The objective is to determine the second highest quantity sold per Customer code.

Since we will first have to determine the Customer wise and Material Number wise quantity sold, a good way to get started is to use a Pivot Table.  One can drag Customer Code and Material Number to the Row labels and Material Number (again) to the Value area section.  We can then sort the numbers in the Value area section in descending order.

Now comes the bit of filtering the Pivot Table to only view the Material Numbers per Customer code which rank second.  To do so, one can try the following steps:

1. Add the Material Number field once again to the Value area section
2. Right click on any cell in the MaterialNumber2 column and choose the "Rank Largest to Smallest" in the "Show Values as" option.  This option is available only in Excel 2010 and higher versions.
3. Click on the Filter drop down of the Row labels heading and select Material Number in the drop down there.  Now go to Value Filters > Equal > Count of Material Number2 > 2

While the steps above sound very logical, the result is incorrect.  The criteria gets applied on the Count column rather than the Rank column.  So the result will be all rows where the Count is 2.

One can overcome this problem by using the PowerPivot.  You may refer to my solution in this workbook.