Tags: FILTER

Compute attrition rate from two different data sources

{ 0 Comments }

Assume two databases: 1. One showing employee headcount (one row per employee) which has all employee details such as Name, ID, Date of Joining, Supervisor name, Department etc. (Range A1:R781 of Source worksheet) 2. The other showing data for employees who resigned. (Range U1:Z36 of Source worksheet) The task is to compute the attrition rate for selected Group and […]

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 →

Compute year wise weighted average on a large dataset

{ 2 Comments }

Assume a dataset with a Key Performance Indicator (KPI) [appearing in one column] data for years ranging from 1985 to 2010 for 114 countries.  This dataset has 170,000 rows of data and one row below the last row for every country, there is a total of the KPI column.  So, if there are 25 rows […]

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 →

Compute configuration count using Set Theory and Venn Diagrams

{ 0 Comments }

Assume a table which lists attendees for a Company’s Annual day function.  In this Table, data for every attendee is shown on a separate row so if an employee attends the function with his/her spouse and three children, then there will be 5 rows for that employee. The question is to determine the count of the following family configuration: […]

Read More →

Computing penalties by Employee, Group and Labour type using a PowerPivot

{ 0 Comments }

Assume a database of Maximum allowed pay and Actual pay for each employee.  The employees have been further categorized into Groups and labour categories. The task is to create three Pivot Tables (one each with Employee name, Group and Labour category in the row labels) with the following information in the Value area section: 1. […]

Read More →

Count unique values with conditions on large databases

{ 8 Comments }

Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all. In this workbook, I have shown the technique to count unique values with conditions on a large database 1. Using PowerPivot – Will only work in […]

Read More →

Calculate a unique count with conditions in a Pivot Table

{ 12 Comments }

Assume a three column table arranged as follows: Circle, Date of Fault and ID.  Dates in the date range span one week – November 26, 2012 to December 2, 2012.  A particular equipment can be only one specific Region and the same equipment an go faulty multiple times within one week.  Data for one week […]

Read More →