Tags: CALCULATED FIELD FORMULA

Recompute figures in the Value area section of a Pivot Table after receiving a user input

{ 0 Comments }

Assume a simple 5 column database with the following data 1. Circle Name – A text field 2. PO_Number – An is an alphanumeric field 3. Quantity sold – A numeric field 4. Unit Price – A numeric field denominated in US$ 5. Revenue – A numeric field which is computed as Quantity sold * Unit Price To determine the Circle […]

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 →

Create a Pivot Table from multiple individual ranges without using ancillary columns

{ 16 Comments }

Assume that there are three separate tables showing the following information: 1. Date of visit data for visitors to a certain recreation facility.  The same visitor may visit the facility multiple times 2. Fee per visit in different cites 3. Region in which each city lies The question is to analyse the three tables above via […]

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 →

Compute Pro rata growth rate within a Pivot Table

{ 0 Comments }

Assume a four column input data range (refer sheet named “Input” of this workbook) arranged as follows: 1. First tow columns are Group and Type which have text values 2. Third column is a month column with entries such as Dec_11 and Sep_12.  These denote 12 months ended December 2011 and 9 months ended 2012 3. […]

Read More →