Tags: CALCULATE

Flex a Pivot Table to show data for x months ended a certain user defined month

{ 4 Comments }

In this simple 3 column dataset shown below, one can see the month wise demand and energy charge for 2 years – 2017 and 2018. The objective is to compute the month wise demand charge for x months ended a certain user defined Year and Month.  So, if a user selects the Year as 2018, […]

Read More →

Show Project wise status in a Pivot Table

{ 0 Comments }

Visualise a simple 6 column Table as shown below – Project Name and the finish date for each of the 5 stages that the projects go through.  Each project goes through 5 stages – Requirement (Req), Development (Dev), UAT, Implement and Warranty. The objective is to report on the status of each project at the end […]

Read More →

Identify Customers that Organisations can upsell or cross sell their products to

{ 0 Comments }

Here’s a simple Sales data of a retail Store which sells Apple Products.  Since a customer can transact multiple times, there will be repetitions in the Cust ID column.  While Cust ID 123 and 782 purchased multiple products from the same Store in one transaction, Cust ID 53 purchased multiple products from different stores (Store […]

Read More →

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

Read More →

Show sales only for corresponding months in prior years

{ 2 Comments }

Refer to this simple Sales dataset The objective is to create a simple matrix with months in the row labels, years in the column labels and sales figures in the value area section.  The twist in the question is that for years prior to the current year (2018 in this dataset), sales should only appear […]

Read More →

Filtering on 2 date fields within one Table

{ 0 Comments }

This table contains a list of all the inspections created and completed within different time periods. The objective is to create two Tables from this single table – one showing the Accounts created within the chosen time period and another showing the those that were closed within the same time period.  Here are screenshots of […]

Read More →

Compute transaction fee based on a tiered pricing model

{ 0 Comments }

Consider a simple dataset as shown below: For each tier, the tier rate is incrementally applied to the volume within the tier volume range.  Given the following transaction volumes, one may want to compute the transaction fee The expected result is shown below As one can observe, for a transaction value of 400,000, the fee […]

Read More →

Perform an aggregation on Top x items after satisfying certain conditions

{ 0 Comments }

Visualise a 5 column dataset as show below.  This is a very small sample of the actual dataset.  It shows the date on which supplies were received for each item from Vendors and whether those supplies had errors in them.  Finally those identified errors have been bucketed into relevant categories.  The Item ID# is a […]

Read More →

Compute the average of values against the 5 most recent dates of each Category

{ 0 Comments }

Here is a simple 3 column dataset showing Categories, Date and Value Catagorie Date Value Fish 08-12-2015 6 Crab 05-12-2015 7 Crab 04-12-2015 6 Bird 27-11-2015 4 Snow 25-11-2015 10 Cat 21-11-2015 7 Dog 12-11-2015 5 Dog 28-10-2015 5 Fish 12-10-2015 3 Bird 11-10-2015 9 Dog 22-09-2015 9 Crab 17-08-2015 8 Cat 11-08-2015 1 Fish […]

Read More →

Determine the total number of projects by Status

{ 0 Comments }

Here’s a simple 3 column table showing Date, Project name (Cat.) and Status of the project.  Each project can have multiple status entries on different dates.  So as you can observe, project “alpha_9383993” was In Progress on Oct 2, 2017, remained so on October 5, 2017 but was completed on October 6, 2017. Date Cat. […]

Read More →