Author: Ashish Mathur

Count tasks by status

{0 Comments}

Assume a simple 3 column dataset as shown below - the date of each task and the status of that task.
The objective is to get the status wise count of tasks by the last time stamp.  So for the Status "To-do", the count should be 2 - Task ABC and DEF.  Only these two tasks on their last time stamp have the status as "To-do".  Tasks CED and ADR should not be counted because their last time stamp had a status other than "To-do".  So the final expected result in MS Excel is:

Since the original data is being fetched from an external data source, no additional tables or columns can be created from/in the source data table.

The final result in PBI Desktop is this
You may download my PowerPivot solution workbook from here and PBI Desktop solution file from here.

Segment towns according to volume contribution and market share with a slicer

{0 Comments}

This post is an extension to the one I posted here - Segment towns according to volume contribution and market share. Here's a simple dataset of Shampoo sales in the state of Rajasthan, India.

For a chosen segment, one may want to segment the 4 towns based on the following conditions:
Based on the two screenshots shared above, the desired result is shown in the screenshot below:
The difference between this solution at the previous one (the link of which I have shared above) is that in this one we want to drag the Classification (range E16:E17) to either the row/column/report filter section of the Pivot Table use it as a slicer.  The current limitation with measures that one writes in PowerPivot's is that measures cannot be used in either row/column/report filter section or as a slicer of/in a Pivot Table.  So in the previous solution, I had written a measure to return the result as Headroom, Stronghold, Emerging or small in only the value area section of the Pivot Table.  One could not drag that measure into the row labels of a Pivot Table.  In this solution, one can drag the Town classification to the row/column/report filter section or even to the slicer (see images below)
You may download my solution workbook from here.

Segment towns according to volume contribution and market share

{0 Comments}

Here's a simple dataset of Shampoo sales in the state of Rajasthan, India.
For a chosen segment, one may want to segment the 4 towns based on the following conditions:
Based on the two screenshots shared above, the desired result is shown in the screenshot below:
The desired result is shown in range E16:E19 and the explanation of the classification is shown in range F16:F19.

The final result obtained by using the PowerPivot is shown in the screenshot below:
You may download my solution workbook from here.

Tabulating data from multiple unstructured Excel files

{0 Comments}

Many a times data downloaded from Applications/ERP's are not in a filter/Pivot ready format.  In such cases, a lot of time has to first be invested in getting that data in proper order before even beginning to analyse that data.  What makes this situation worse is that data is downloaded every month in that unstructured manner and the effort which was spent on "cleaning up" the data in the previous month has to be replicated this month as well.

Here is one such dataset.  Some easy to spot problems here are:

  1. All data is in a single column instead of being in multiple columns
  2. Data which should ideally appear in a single row is appearing in multiple rows.  See data in rows 26 to 32.  Information from rows 27 to 32 should appear in row 26
  3. Rows 1 to 6 are not required at all

Data should appear in the following format to make it "filter/Pivot ready".  Furthermore, when multiple such files (as the one shown above) are placed in a certain folder, one should simply be able to open the output file (shown below) and go to Data > Refresh All.

I  have been able to solve this problem using Data > Get & Transform.  Following these steps to get my solution up and working on your system:

  1. Download the input file from here
  2. Download the output file from here
  3. Create a folder on your desktop and place the input file in this folder.  Store as many input files in this folder.  Change data in those input files, if you so wish.
  4. Save the output file anywhere outside the folder that you created in step 3 above
  5. Ensure that all input workbooks are closed
  6. Open the output file and go to Data > Queries and Connections.  Right click on the Result Query > Edit
  7. Double click on Source and select the folder that you created in 3 above
  8. Click on Close and Load
  9. Anytime there is any change in the source folder files, just go to the Output file and click on Data > Refresh All

Calculate rolling sum for the past week by ignoring blank cells

{0 Comments}

Assume a simple dataset as shown in the image below (the input data is in columns A and B only.  The desired outcome is in columns C and D).

The objective is to calculate the 7 days rolling sum and average (as shown in columns C and D) ignoring blank cells.  So in cell C8, the rolling sum is the summation of values from range B2:B8.  In cell C9, it is from B3:B9.  However, in cell C10, it will be from range B3:B9 (not from range B4:B10).  Likewise, in cell C11, the rolling sum will be from range B4:B11.  So the range to be considered for calculating the rolling sum has to roll back automatically until it picks up 7 numeric cells - the blanks have to be ignored.  The rolling average is a simple division - Rolling sum/7.

I have solved this question with Excel formulas here.  This time however, I am sharing a solution by using the DAX formula language available in the PowerPivot and PowerBI Desktop.  You may download my PowerBI Desktop file from here.  The same solution can also be obtained in MS Excel using the PowerPivot as well.

Append data from multiple worksheets of multiple workbooks where each worksheet has a different heading

{0 Comments}

In a folder there are multiple workbooks with an unknown number of worksheets in each workbook.  Each worksheet has data for one year and has 13 columns - the first is for the Product and the other 12 are for each month of the year.  So sheet1 of Book1 has Product in column1, 1 Jan 2015 in column2, 1 Feb 2015 in column3 and so on until 1 Dec 2015 in column13.  Likewise, sheet2 of Book1 has Product in column1, 1 Jan 2016 in column2, 1 Feb 2016 in column3 and so on until 1 Dec 2016 in column13.  The worksheet which has data for the current year will have fewer columns since data for all months is not available.  The number of rows on each worksheet vary depending upon the number of products sold in that year (as can be seen in the two images below).

The objective is to append data from all worksheet of all workbooks to create a simple 3 column Table - Date in column1, Product in column2 and Value in column3.

The solution should be dynamic enough to accommodate the following:

  1. More files added to/deleted from the folder
  2. More worksheets added to/deleted from the existing files
  3. Data added by rows/columns to the existing worksheets
  4. Heading changes in the existing worksheets i.e. in sheet1 of the DATA1 workbook (first image above), one may want to change the year appearing in the first row from 2015 to 2013

This question would have been an easy one to solve had the headings on all worksheets of all workbooks been the same such as Month1, Month2, Month3, Month4 etc.  In that scenario, the technique shown in this YouTube vide would have worked very well.  However, since the headers on each worksheet of each workbook are different, this technique yields an unexpected result (shown below) - Please note that I have not used the Table.PromoteHeaders([Data]) command as described in the video because that would take away the row with dates i.e. row1, row5, row11, row16.  If I do not run that step, I get the output as shown in the image below.  The dates appear as part of the Data rather than being identified as headers.

I have solved this question using the Power Query Editor.  You may download my source data from here and solution workbook from here.

  1. Download the source data workbooks and save them in a folder.
  2. Download the solution workbook and save it anywhere else.
  3. Open the solution workbook and go to Data > Queries and Connections.
  4. In the pane that opens up, right click on the Query > Edit.
  5. In the Applied steps window, double click on Source and specify the path where you have saved the source data workbooks (step1 above).
  6. Click on OK > Close and Apply.

Summarise data by most recent status

{0 Comments}

Here's a simple 3 column dataset showing Date, ID and Status - the status of each ID by Date.

So, the narrative for ID A is:

  1. It was "New" on Jan 1
  2. It remained "New" until Jan 14
  3. On Jan 15, the status changed to "Open"
  4. It remained "Open" till Jan 31 and the status changed to "Closed" on Feb 1
  5. It remained "Closed" till March 31 and the status changed to "Stop" on April 1
  6. It has remained in 'Stop" status till Today

Note that for the month of March, there is no record for ID A but the status of it has to be treated as Closed (refer point 5 above).

The objective is to count the number of ID's by status and month.  The expected result is:

Please note that the trick part here is to get the result as 2 in cell D6 (Status closed for March). I have solved this problem using Power Query and PowerPivot.  Since these two Business Intelligence (BI) tools are available in PowerBI desktop (PBI) as well, you may download a folder with both files (the MS Excel workbook and PBI file) from here.

Segment customers into dynamic buckets

{0 Comments}

Consider a 4 column table - Respondent ID, Device ID, App Name and Category.  So this dataset shows which apps are installed on which device ID by which user and which category do the apps fall into.  It is a small dataset with only 4 columns and 2,000 rows.

The question on this dataset is - "I would like to segment the total user base by Categories into the following 9 buckets:

  1. Those who only have 1 app installed; and
  2. Those who have 2 apps installed; and
  3. Those who have 3 apps installed; and
  4. Those who have 4 apps installed; and
  5. Those who have 5 apps installed; and
  6. Those who have 6 apps installed; and
  7. Those who have 7 apps installed; and
  8. Those who have 8 - 10 apps installed; and
  9. Those who have 10+ apps installed

The expected result is a Pivot Table with buckets in the column labels, Categories in the row labels and number of people in the value area section (as shown below)

Here's how one can interpret the Pivot Table shown above:

  1. Cell B50 - There are 75 people who only have 1 "Tool" app installed
  2. Cell J44 - There is just 1 person who has 10+ Photography apps installed.

I have solved this problem using Power Query and PowerPivot.  Since these two Business Intelligence (BI) tools are available in PowerBI desktop (PBI) as well, you may download a folder with both files (the MS Excel workbook and PBI file) from here.

Compute hours spent on projects given resource allocation

{2 Comments}

In the dataset below column A has the Employee Name, column B and C are the assignment start and end dates, Column D is the location and columns E to J are the Month-Year columns.  So each row represents data for an employee on a particular project.  The numbers in range E2:J8 represent how much that particular employee is aligned to the particular project i.e. a value of 1 means that the employee is dedicated solely to that project, 1.4 means that the employee will be spending extra hours on that project and 0.1 indicates that the employee will be working on multiple other projects.

The objective is to create another column (column K in the second screenshot) which will show the number of hours the employee will spend on the project.  The number of hours will be computed as number of working days in a month (treat Saturday and Sunday as weekends) * time allocation to that project (the numbers in range E2:J8) * 8.5 hours per day for an Offshore project and 8 hours per day for other projects.

The raw data sheet looks like this

The expected result is

The figure in cell K3 has been computed as:

  • Number of working days between November 11, 2018 and November 30, 2018 are 15.  So 15 * 1 = 15
  • Number of working days between December 1, 2018 and December 12, 2018 are 8.  So 8 * 0.5 = 4
  • Total effective working days are 15 + 4 = 19
  • Since it is an Offshore project, the hours per day would be 8.5.  Therefore total effective hours: 19 * 8.5 = 161.5

I have solved this problem using 3 methods:

  1. Excel formulas - Refer worksheet named "Formula output"
  2. Power Query and PowerPivot - Refer worksheet named "Power Pivot output"
  3. Power Query only - Refer worksheet named "Power Query output"

You may download my solution workbook from here.

Customer analysis by Country and time period

{2 Comments}

Here is a Sales dataset of 8 columns and 29 rows.  It basically details the revenue earned and cash collected by service type, Customer, Country and Period.  For a selected Country and time period, there could be customers availing of both services or of any 1 service.


There are 2 broad questions that one may want to get answers to:

  1. Determine the number of customers who availed of a certain number of services
  2. Determine customers with whom business was forged for the first time and those who churned out

For a chosen country and Year/Month, the first question stated above further sub-divides into:

  1. How may customers availed of both services - Consultancy and Implementation
  2. How may customers availed of only one of the two services

So if a user selects the Country as India and Year/Month as January 2015, then Customers who availed of both services would be 1,3 and 4.  Note that Customer 2 should not be considered (even though he/she availed of both services) because the revenue earned from one of the services (Implementation) was nil.  For the same selection (India and January 2015), the Customers who availed of only 1 service would be Customer 2 - this customer availed of only the Consultancy service (Revenue was earned from this Customer only for this service).  After applying a filter on the source dataset, the rows for India and January 2015 are:

The expected result is shown below in PowerBI desktop software.  If you are not concerned with who those customers are (you just want the count), then you may simply remove the Customer Name field from the visual.

The second question is to determine the number of new and lost customers.  If a customer was not in the database in any prior month, the customer is identified as new.  To clarify, a customer who availed of the Consultancy service in a prior month also availed of the Implementation service for the first time in the current month would not be counted as a new customer.  If a customer ceases to generate revenue in any month, the customer would be counted as lost (churned) in that month.  So when USA is selected in the Country slicer and Year/Month is February 2015, the expected result is:

I have solved this question with the help of the PowerPivot.  You may download my PowerBI desktop solution file from here and source Excel workbook from here.  This problem can also be solved in MS Excel using the PowerPivot.