Pivot Table Issues

Calculate Employee Billing Amounts in Excel PivotTables

Maira Mehtab Sep 28, 2026 868 views

Question details

The user needs to calculate the total billing amount for each employee by combining logged hours from one table and individual billable rates from a related table within a single PivotTable.

Product: Excel
Device & OS: not provided
Scenario: Generating billing or payroll reports where employee hours and hourly rates are stored in two distinct tables.
Observed behavior: A standard PivotTable cannot natively multiply values across separate unjoined tables without producing aggregated multiplication errors. A relationship or calculated measure is required to compute row-by-row billing correctly.

Before you start

Ensure both your 'Employee Hours' and 'Billable Rates' data ranges are formatted as Excel Tables (Ctrl + T) and added to the Excel Data Model.

Solution 1Recommended

Use the Data Model and DAX Calculated Columns

By adding your tables to the Data Model, you can use DAX functions like RELATED to fetch rates and calculate accurate row-by-row billing amounts before summarizing them in a PivotTable.

Standard PivotTable Calculated Fields sum up the hours and rates first, then multiply the grand totals, which results in inaccurate billing amounts. Using the Data Model allows Excel to calculate the billing amount for each individual entry before summing it up.

Establish a table relationship

Go to the Power Pivot tab and open the Data Model. Create a relationship between the 'Employee Hours' table and the 'Billable Rate' table using the Employee ID or Employee Name column.

Create a calculated column for rates

In the Data Model view, select the 'Employee Hours' table. Add a new calculated column to pull the rate from the related table using the formula: Rate per hour = RELATED('Billable Rate'[Rate]).

Calculate the total billing amount per row

Create another calculated column in the same table to multiply the hours by the retrieved rate: Total = Data[Rate per hour] * Data[Hours].

Create a sum measure

At the bottom of the Data Model window (the calculation area), create a new DAX measure to sum the totals: Amount = SUM(Data[Total]).

Insert the PivotTable

Click 'PivotTable' from the Data Model window to insert it into your worksheet. Drag the Employee Name to the Rows area and the new 'Amount' measure to the Values area.

Accurate Calculation: This method ensures that the multiplication happens at the individual row level before aggregating, guaranteeing 100% accurate billing totals.

Efficient Data Analysis

Calculate Billing Amounts Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful built-in lookup functions and intuitive PivotTable features, allowing you to merge related tables and calculate employee billing amounts quickly without complex data modeling.

1. Combine your data using VLOOKUP: In your 'Employee Hours' sheet, create a new column called 'Rate'. Use a VLOOKUP formula to pull the correct rate from the 'Billable Rates' sheet based on the employee's name or ID.
2. Calculate row totals: Create another column called 'Billing Amount'. Multiply the logged hours by the newly retrieved rate (e.g., =B2*C2) and drag the formula down to apply it to all rows.
3. Insert a PivotTable: Highlight your updated 'Employee Hours' table, go to the Insert tab on the top ribbon, and select 'PivotTable'.
4. Analyze the billing amounts: In the PivotTable Field List, drag the Employee Name or Task to the 'Rows' area, and drag your new 'Billing Amount' column to the 'Values' area to see the correct totals.

Fully compatible with Microsoft Excel (.xlsx) file formatsBuilt-in VLOOKUP and XLOOKUP for easy data mergingIntuitive and fast PivotTable creationLightweight and free alternative for professional data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable Calculated Field show the wrong billing total?

Calculated Fields in a standard PivotTable operate on the sum of the data, not the individual rows. It calculates (Total Hours * Total Rates) instead of summing (Individual Hours * Individual Rate). To fix this, you must calculate the total per row in your source data or use the Data Model.

Can I calculate employee billing amounts without using Power Pivot?

Yes. You can add helper columns directly to your source data worksheet. Use VLOOKUP or INDEX/MATCH to pull the billable rate into the hours table, multiply the hours by the rate in another column, and then base your standard PivotTable on this enriched data range.

What is the RELATED function in Excel?

The RELATED function is a DAX formula used within the Excel Data Model. It works similarly to VLOOKUP by retrieving a related value from another table, provided a relationship has been established between the two tables.

Excel & Data Problems