Category: POWERPIVOT

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

{ 1 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. […]

Read More →

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 […]

Read More →

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 […]

Read More →

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 […]

Read More →

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 […]

Read More →

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 […]

Read More →

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, […]

Read More →

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 […]

Read More →

Compute Relative Size Factor per vendor

{ 2 Comments }

Relative size factor (RSF) is a test to identify anomalies where the largest amount for subsets in a given key is outside the norm for those subsets. This test compares the top two amounts for each subset and calculates the RSF for each. In order to identify potential fraudulent activities in invoice payment data, one […]

Read More →

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 […]

Read More →