Pivot table averaging issue/question

[edmartin]

I have a pivot table that brings in data where a client will have multiple lines of data. For example, Fred Smith might have 3 invoices associated with him in the raw data while Mary Jones might have 2 invoices.

Everything is summing up correctly, but I can't figure out how to "fix" my averages. The way it appears to be working, the average calculation is (Fred1 + Fred2 + Fred3 + Mary1 + Mary2) / 5. But what I really want is (Fred1 + Fred2 + Fred3) + (Mary1 + Mary2) / 2.

Is there any way to do that type of average calculation in a pivot table?

 

[johnny51981]

Hi @edmartin
I am coming up with an accurate average based on test set and pivot I created.

Where did you drop each of your columns when creating the PivotTable?

 

[edmartin]

Trying a simple test. Let's say my data source has 2 columns: "Project Name" and "Total". The data source has 927 rows of data in it. As above, many rows will be data for the same client (the Fred & Mary factor).

For my pivot table, I am dragging "Project Name" to rows and then dragging "Total" to columns twice. I right-click on the second iteration of Total and change it to average. The pivot table ends up with 546 rows in it. The bottom-line total is correct. But the average isn't.

 

[johnny51981]

Can a Project Name have more than 1 invoice to it?

 

[edmartin]

Yes. Sorry. I didn't stay consistent with names above. Project Name is the same as "client".

 

[johnny51981]

LOL so that takes us back to square one.

But what about...can a single invoice be listed multiple times for a Client?

I'm just wondering if the count of what is used as the average's denominator is too high, causing your averages to be off.
Example:
Fred - Invoice 1234 - $500
Fred - Invoice 1234 - $750
Fred - Invoice 2389 - $100

The Pivot would return an average of $450 because there are 3 entries. But the average that you are actually looking to achieve would be $675 because there are 2 invoices.

Does that make sense?

 

[edmartin]

Sort of...

Instead of Fred & Mary, let me use my real data source. I have 927 rows of source data. My pivot table ends up with 546 rows (because clients ("Project Name") usually have more than 1 invoice). For each of those 546 rows, the total is correct (sum of all invoices for that client). And for each of those rows, the average is correct (sum of all invoices/divided by the number of invoices for that client). On the Grand Total line, the Total is correct (the sum of all invoices for all clients). But the bottom-line average is not what I need. What it shows is total of all invoices divided by total number of invoices.

What I want is total of all invoices divided by the number of clients. I just can't figure out how to get there in the pivot table.