Pivot Table - Sum and Avg in Grand Total (counting unique records)

[Distraction]

I have data in a Pivot Table displayed as below:

User ID $1-100 $100-500 $500+

12345 3 1 0
12346 1 2 1
12347 4 0 0
12348 1 1 1
12349 2 1 1
TOTAL 11 5 3

The numbers in each dollar bucket represent the number of transactions by that user.
So user_id 12345 had 3 different transactions where the transaction value was between $1 and $100,
one transaction between $100 and $500, and none higher.


I'd like to either add a line under TOTAL that also gives me AVERAGE, (simple in normal Excel, HARD in Pivot Tables)

AVERAGE 2.2 1 0.6



OR just figure out a way to make the TOTAL line display averages.


If I right-click on TOTAL and choose Summarize by: Average, all my averages = 1.
That is because the data being summed in the data section is a value of '1' for each transaction.
If I do a 'count', it does the same thing as the sum, since each value is 1.

What I'm looking for is the average number of transactions per account by bucket.

Any ideas?

I need to keep this in a Pivot Table because the Month of the data is a Page field and I need to be able to look
at the averages from one month to the next by changing the selection in the page field.

Many thanks in advance.

 

[SkipVought]

Hi,

Right click the Total row heading and select average.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Distraction]

I tried that and it gives me all '1's.

The reason is that in the Pivot Table it averages each row, not just the total. And in the example above, user_id 12345 has a total of 3 transactions in the 1-100 bucket, but it's counting the num_trans (value of 1) from the database for that account number and bucket. So the AVERAGE in bucket 1-100 is 1, the AVERAGE in bucket 100-500 is 1, etc.

Does that make sense?

 

[SkipVought]

I am sorry!

WNot a very elegant solution, but why not insert another PT above the existing table to simply avarage across dollar ranges?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

BTW, in order to "automate" updating both PTs, you could put a button on the sheet that runs

Sub Button_Click()
  For Each pvt In ActiveSheet.PivotTables
    pvt.RefreshTable
  Next
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Distraction]

Thanks for the speedy replies...I appreciate the help.

I'm not looking for average dollars, I'm looking for average number of transactions. (Trying to strategize a rewards program based on number of tx, not purchase volume...we want to incent multiple purchases regardless of price bucket.) So what can I average that will give me what I'm looking for?

Or even COUNT? I can't seem to figure a way to get a unique record count...because user_id appears in each transaction record there are the same number of user_ids as transactions, so it's a 1:1. If I could get the Pivot Table to Sum the number of transactions and count distinct user_ids, I could go from there.

 

[SkipVought]

You could do a Calculated Formula in the PT that is
[tt]
=COUNTA(User ID)
[/tt]
I believe that this would count unique occurrences.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Distraction]

Nope...still returning all 1s.

OK...here's a sample of raw data that the Pivot Table is built from:

user_id num_trans trans_bucket year_mo
12345 1 1-100 2003-03
12345 1 1-100 2003-03
12345 1 100-500 2003-03
12345 1 1-100 2003-03
12346 1 1-100 2003-03

etc....only with a ton more data.

num_trans is always equal to 1.

So for the above data set, user_id 12345 would have 3 in the 1-100 bucket and 1 in the 100-500 bucket.

The pivot table 'de-dupes' the user_id for the report, but always COUNTS it for the number of records
in which it appears.

 

[SkipVought]

Some things cannot be calcualted in one simple transaction.

Do a PT on
Row: User_ID
Data: Count of num_trans

Furthermore, I woud STRONGLY recommend AGAINST using year_mo, which I assume, from the example, is a STRING rather than a REAL DATE. Real Dates in PTs can be Grouped by a number of means including Year & Month to accomplish what the year_mo string will do AND MORE!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Distraction]

That's what I did to begin with...the data in the first post is the Pivot Table you recommend, with the buckets added across the top.

I'm no too worried about the date format...not planning on doing a whole lot with it.

Mostly I want to be able to figure out what the average number of Transactions per account per bucket is.

 

[Distraction]

OK...I came up with my own klugy fix.

I created a separate sheet to summarize the Pivot Table data.

I created a row of formulas using the GETPIVOTDATA function which pulled the TOTAL of each bucket.

Then I did a (COUNTA(range))-1 function, where range = the user_id column starting with where the first record is, all the way down to row 67000-whatever. (the bottom). The 'minus 1' part is to not count the TOTAL row in the Pivot Table.

So now I have the totals and the record-count on my summary page. I can use normal Excel math to calculate the average.

Every time I change the Page data of the Pivot table (from month to month), the number of user_ids change, but the counta - 1 works.

I know alot of folks have posted messages about how to figure this out...let me know if I need to explain this better.

Matt