1
I have a data table ('Legal Fees') with three columns:
- Date
- Attorney Name
- Hours Billed
I have another data table ('Billing Rates') with two columns:
- Attorney Name
- Hourly Billing Rate
I would like to display a summary of the information in a pivot table that shows:
- Attorney Name
- Hours Billed
- Hourly Rate
- Dollar Amount Billed (hours*rate)
I realize I can do this right on the data table pretty easily with a VLOOKUP to the 'Rate' table and another column multiplying the hours billed by the rate, but as an exercise, I'd like to do this without manipulating the data tables at all, only using a pivot table to summarize the data.
What is the best practice for:
- Displaying the attorney's billing rate (which is constant for each attorney) on the pivot table (without inserting a column on the 'Legal Fees' data table), and - not possible with vanilla Excel per /u/agtoever
- Displaying the calculated field (hours*rate) on the pivot table without calculating it on the data-table first (so the PT is actually doing the calculation)
Thanks!
Thanks! Any insight on how to handle #2, the calculated field, without any addons? – jackerman09 – 2015-02-06T17:54:54.963
Not without addons. Once you have hours and rate in one source table (either by VLOOKUP in the Excel sheet or by using Power Pivot), you can add a "calculated field" inside a pivot table, where you can calculate anything using the fields in the source table: Billed = Hours * Rate.
– agtoever – 2015-02-06T21:06:30.623