Tags: TOPN

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.

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.

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.

Determine the top selling location for each product

{0 Comments}

Visualise a 3 column dataset as shown below - Location, Product and Sales.  Each location can have multiple products (Product A has Banana, Apple and Carrot) and each product can be sold in multiple locations (Banana is sold in locations A, B and F).

The objective is to determine the location with highest sales for each product.  So for Banana, maximum sale value is 25 and location of maximum sales value is B.  Likewise for Orange, maximum sales value is 49 and location of maximum sales value is A.  The expected result is:

I have 4 solutions to this problem:

  1. Advanced Filters - This is a static solution.  For any changes in the source data range, one will have to re-enter the 3 inputs in the Advanced Filter window
  2. Formulas - This is a semi-dynamic solution.  To make it fully dynamic, one will have to write an array formula to first extract all unique product names in a column.  The array formula to extract product names in a column can be obtained from here.
  3. Power Query - This is a dynamic solution.  For any changes in the source data sheet, one just has to go to Data > Refresh All
  4. PowerPivot - This is a dynamic solution.  For any changes in the source data sheet, one just has to go to Data > Refresh All

You may download my solution workbook from here.

Determine the lowest bidding vendor(s) for each product in a Pivot Table

{0 Comments}

Imagine a dataset like this.  This dataset shows vendors that submitted proposals for supplying various parts to a Company.  There is one column for each of the twelve months.

untitled

Via a simple Pivot Table, one can determine the lowest bidding vendor per product (part) for any chosen month.  However, one may also want to know the names of those vendors for each product (as seen in column G below).  Notice, that Vendor 2 and Vendor 3 submitted the lowest bid for Product 1 and therefore both names should appear in the result.

untitled

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

Story telling with Excel Power BI

{6 Comments}

With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations.  Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story.  To be able to interact with/create visualizations, you will need to install Microsoft Office Professional Plus 2013 (this version will already have two of the four components of Power BI - PowerPivot and Power View).  Additionally, you will have to install the following add-ins from Microsoft (the other two components of Power BI)

1. Power Query; and
2. Power Map

I have tried to showcase the prowess of Power BI tools of Excel 2013 in these two workbooks:

1. An overview of the BRIC Economies
2. Sales data analysis

You may watch a video of my work at this link

Perform an iterative sum of Top n values across multiple columns

{4 Comments}

A tournament has 18 participating teams with 25 players in each team.  Each team has to play five rounds of the Tournament and not all players play all rounds.  Scores earned by each player in each round are shown in individual cells.  If a player does not play a round, that cell is left empty.

The task is to sum the highest 18 scoring players for each round.  Only the highest 18 players per team count towards the teams score.  If few of the players have the same score at position 18 then only one of them should be included in the overall score.

One solution is to sort each round of scores for each team in descending order and sum the highest 18 values.  This is obviously a time consuming process.

There could be two others ways to solve this problem

Formula driven solution - This uses a spare column, a lengthy formula and the Data > Table functionality.  Since Data > Table is a series of array formula, this solution makes the workbook very sluggish.  You may refer to my solution in this workbook.

PowerPivot solution - This solution is far better than the formula driven one in as much as no spare columns, lengthy formulas or Data > Tables have been used.  The solution in this workbook adds the scores of the highest 18 scoring players per round (If few of the players have the same score at position 18 then all scores are included in the overall score).  To use this PowerPivot solution, you need to be using the PowerPivot add-in for MS Excel.  This add-in is only available for Excel 2010 and higher versions.

Sum highest n numbers based on conditions

{4 Comments}

Assume a two column database with names in column A and numbers in column B.  Names in column A may be repeated.  If a user types a certain name in a cell, a formula should sum the highest three values from column B for that name.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the PowerPivot add-in, then a simple DAX formula can solve this problem.

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system's performance.

You may refer to my solution in this workbook.