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. Actual salary
2. Maximum permissible salary
3. OverUnderMax – This is calculated as the difference between Actual Salary and Maximum permissible salary
4. Penalty – Maximum of 0 and OverUnderMax
As can be seen in the Pivot Table worksheets of this workbook, there is a problem with the result of the calculated field formula result in the Grand Total cell.
You may refer to my solution in the PowerPivot worksheets. Please note that to see my result in the PowerPivot worksheets, you will have to use the PowerPivot add-in for MS Excel 2010/2013.