Tags: FILTER

Determine latest condition of each equipment and show a month wise count

{0 Comments}

There are 100 machines in a factory.  Every machine has different test frequency. In a given month, not every machine is tested but we still have the last known rating (from some previous month) of that machine.  We have to show the latest rating of each machine for each month in a stacked column chart. This way, the total number will remain 100 every month in the chart, but the rating distribution (color based on legend) will change based on last available rating of that machine.

For example, in January, 35 machines were tested. So we have latest ratings of these 35 machines. But as the rest of the machines also have some previous rating, the graph needs to show all 100, with last available rating.

The expected result should look like this

You may download my PBI Desktop file from here.  The very same DAX formulas can be written in the DAX formula language of MS Excel as well.

After filtering a dataset, allow the user to display only specific columns in the result

{0 Comments}

Here is a simple 5 column dataset showing basic employee information.

The objective is to show only those rows of data in which the Score (column D) is greater then 3.  While one can solve this with a simple filter, the solution will not be dynamic.  To get a dynamic solution, one may use the FILTER() dynamic array function that comes with the Microsoft 365 subscription service.

In cell G2, one may simply write this formula

=FILTER(A2:E9,D2:D9>3)

This is a far better solution because it is formula driven and thereby dynamic.  So all good till here.  Now let's make it a little interesting.

The revised objective is to show only those rows of data in which the Score (column D) is greater then 3 and only display 3 columns - Name, Department and Score (columns 1,3 and 4) in the end result.  This can be solved using Data > Advanced filter but the result will be static.  To get a dynamic solution, one may use a nested FILTER() function in cell G2

=FILTER(FILTER(A2:E9,D2:D9>3),{1,0,1,1,0})

This formula will return the same number of rows (3 rows) as the previous FILTER() function returned with only 3 columns - Name, Department and Score.  The 1's and 0's in the formula denote whether one would like to see the particular column in the end result or not.  So once again, all good till here.

Now, let's take it a notch higher.  What is there were 35 columns in the original dataset and one wanted to see data appearing in columns 1,6,22,25,29 and 34.  It will be quite time consuming to enter the 1's and 0's in order within the FILTER() function.  So the question here is how does one save time and effort?  Ideally one should be able to just enter the column headings one wants to see in the end result.

As you can observe in the image above, one has to simply specify the columns to extract in column C and the result populates from column K rightwards and downwards.  Using dynamic array formulas and the FILTER() function, one saves effort in entering 1's and 0's in the FILTER() function (the FILTER() function has been written in cell K2 - download link of the file is shared below).  If one types Address in cell I5, then Address would automatically appear in cell N1 and so will the entries in range N2:N4.  So this does seem like a good solution.  So while it is good, it is not a perfect solution.  In column I, if one changes the order of the headings i.e. one enters Name, Dept, Address and Score (rather than Name, Dept, Score and Address), the result under columns M and N would be incorrect (see image below).

Solving this challenge led me to using Data > Get & Transform.  I made use of the latest feature introduced in Power Query called "Data Types" (I received this feature update on December 4, 2020).

As one can see in the image above, one simply has to select any heading one wants and that appears in the next available column.

You may download my solution workbook from here in which i have shown both formula based and the Power Query solution.

Show Balance outstanding everyday even if data for everyday is not available

{0 Comments}

In this simple 3 column dataset, there are 2 accounts - Konto 1 and Konto 2.  Each account has a balance outstanding as on a certain date.  However, if you notice carefully, there is no balance for any account on January 4-5,9-10 2020.

The objective is show the balance outstanding every day.  For days which are absent from the dataset, the balance outstanding should be the balance as on the previous day.  So for the Konto 1 account, on January 4-5, the balance should be 400 and on January 9-10, it should be 250.  The same logic applies for the Konto 2 account as well.  The expected result should be

I have solved this problem using Data > Get & Transform and PowerPivot.  You may download my solution workbook from here.

Here's another related question.  Given this 3 column dataset, the objective is to determine the total of the "PDV Combined Total" column for the past "5 billing days" from today - the past 5 working days have to be determined by looking at the "IsABillingDay" column.  So if today is December 4, 2020, then the past 5 business days would be November 30, 2020 to December 4, 2020.  The result should be 14,95,937.  However, if today was December 2, 2020, then the past 5 business days would be November 24, 2020 to December 2, 2020.  The result would be 14,33,545.  You may download the solution in a PBI file from here.  So while this question has been solved using the DAX formula language in PowerBI Desktop, since the same formula language exists in MS Excel as well, this result can be obtained in MS Excel as well.

Analyse membership changes from year to year

{2 Comments}

Assume a simple 4 column dataset as shown below.  This data shows which ID had which type of subscription in which year.  So ID A, which started as a "Free" subscriber in 2018 switched to a "Premium" subscriber in 2019 and then churned out in 2020.  Likewise, ID D which started as a "Pro" subscriber in 2018, churned out in 2019 but returned as a "Free" subscriber in 2020.
The objective is to study how subscribers switched from one subscription type to another across year.  So the expected result should look like this


I have solved this question using the PowerPivot.  You may download my MS Excel workbook from here.

Show text entries in the value area section of a Pivot Table after meeting certain conditions

{0 Comments}

In the value area section of a normal Pivot Table one can only show the result of aggregation functions such as SUM(), COUNT(), AVERAGE() etc.  Even if one drags a text field to the value area section of a Pivot Table, one cannot show those text fields because they automatically get counted.

Consider the following dataset.  The important columns to consider here are COD (Column C), Level (Column E) and Date (column G).


For a COD, there can be a number of rows (COD 31512268 has 3 rows).  For this COD, there is just one level (E) for the same date/time.
It is also possible that for a particular COD, there can be different Levels (COD 31512259 has 4 rows).  For this COD, there are 2 levels (E and D) for the same data/time.

To further complicate the issue, there can be some cases where for the same date/time, a COD may have different levels.  COD 11058698 has 2 different levels (K and M) for the same date/time.
The expected result is to show a Pivot Table with COD's in the row labels and the Level(s) as on the farthest date/time of each COD.  If a particular COD has 2 levels as on the farthest date/time, then they should be shown in the value area section of the Pivot Table (separated by commas).  So the expected result should look like this.  Notice that COD 11058698 has 2 levels as on the farthest date/time (K and M) and COD 11058700 has 3 levels as on the farthest date/time (Blank, M and 1M).
I have solved this question in MS Excel and PowerBI Desktop with the help of the DAX formulas.  You may download my Excel solution workbook from here and PowerBI Desktop file from here.

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.

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.

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.

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.