Tags: HASONEVALUE

In a Pivot Table, compute highest revenue earned on any day from each customer and the date thereof

{ 0 Comments }

Here’s a simple dataset showing the Date of sale, Customer Name and Sales amount. Date Customer Name Sales amount 12-03-2017 A 1 12-03-2017 A 2 12-03-2017 A 3 12-03-2017 B 4 12-03-2017 B 5 12-03-2017 B 6 12-03-2017 B 7 12-03-2017 B 8 13-03-2017 A 1 13-03-2017 A 1 The objective is to determine Customer […]

Read More →

Quantify combination courses opted by students

{ 0 Comments }

Assume a dataset with two columns which lists down the student names in column A and courses opted for in column B.  Since one student can opt for multiple courses and the same course can be taken up by multiple students, there can be repetitions in both columns.  The objective is to create a matrix […]

Read More →

Identify buy and sell break points

{ 0 Comments }

Assume a two column dataset with Date in the first column and Price in the second one.  The purpose is to identify times to buy and sell – buying would be just after the lowest low is confirmed and sell before or just after the highest high is in place. Confirmation is achieved through crossover of […]

Read More →

Rank numbers in a range after satisfying conditions

{ 0 Comments }

Assume a five column dataset – ID, Age, Gender, Time and Class.  For chosen ID’s, the objective is to: 1. Assign a Rank (in ascending order of time i.e. lowest time will be rank 1 and so on) to each ID 2. Determine the overall place of each ID – Count of unique time entries […]

Read More →

Consider a Pivot Table Value field column as a criteria for computing another Value Field column

{ 8 Comments }

Assume a simple three column dataset showing hours worked by different machine on different dates.  So column A is Date, column B is Machine Name and column C is hours worked.  There are duplicates appearing in column A and B .  Blanks in column C depict machine idle time. The task is to create a simple […]

Read More →

Sales data modelling and interactive visualisations

{ 30 Comments }

Visualise Sales Data of a Non-Alcoholic Beverage Company with basic columnar information such as Date of Sale, Time of Sale, Brand, Stock Keeping Unit (SKU), State, City, Quantity sold, Unit Price and Salesman Code.  In this sales dataset, each line item represents one visit for one SKU.  If nothing is sold in a certain visit, then the SKU […]

Read More →

Data slicing and analysis with the Power Pivot

{ 0 Comments }

Visualise an MS Excel file with two worksheets: Employee headcount – a multi column dataset with information such as Employee code, Date of Joining, Age, Division, Department and Location.  Each row represents data for one employee.  The number of rows on this worksheet is approximately 700. Training Data – a multi column dataset with information […]

Read More →

Perform different calculations in the Subtotal/Grand Total column of a Pivot Table

{ 33 Comments }

Visualise a Pivot Table with a few Fields dragged in the Report filter, Row labels and Value Area section.  In the Column labels are two fields, Month and then Year – so in the column labels, for every month, there is data for three years 2005, 2006 and 2007.  For some months, there is data […]

Read More →

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

Read More →

Analysing customer walkin data by date and service taken

{ 5 Comments }

Assume data is in range A3:E10.  In A4:A10, various service types are mentioned.  In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012.  In range B4:E10 are numbers representing number of customers.  One may want to answer the following questions from this data: 1. For every date, total number of customer walkins and […]

Read More →