Pivot Table: Drill down on subtotal to get only filtered values

[jslee]

Hi, everyone

I have a data set that is a list of transactions with customer names and dollar amounts. I've created a pivot table with row labels of 1) date and then 2) customer name. The value label is the dollar amount of each transaction.

My goal is to create a list of all the transactions from customers whose total transaction amount on a particular day exceeded a certain dollar amount (let's say $1000). (Customers might conduct more than one transaction on a day)

So to start with, my pivot table is grouped by day, then by customer name. Then the value fields are automatically a sum of all the individual dollar amounts for a particular customer on a particular day.

I right-clicked in the customer row, then hit Filter --> Value Filters then set the filter for greater than or equal to $1000, which gives me the list I want (all customers on each day that spent a total of $1000 or more).

But now what I want is the drill down details for all of these transactions across the entire data set (an entire quarter). I thought I could just scroll down to the bottom of the pivot table and double-click the "Grand Total" amount to give me the drill down details, but it ends up outputting the entire, unfiltered data set. (The "Grand Total" sum is the sum of only the filtered amounts, so I'm confused as to why the drill down on it gives me unfiltered amounts).

It seems that if I highlight the transactions on a single day, right click then hit Filter --> Keep Only Selected Items, then double-click the subtotal for that day, it gives me the drill down list I want for that particular day, but I want to be able to get a list for the entire quarter at once.

Can anyone help me out?

Thanks a bunch.

 

[Fazza]

hi,

I'm not 100% clear on the setup/requirement. Posting sample data might assist? Maybe also knowing your Excel version. I use Excel 2003, BTW. If I understand correctly, what about moving the customer row to be the first row field and then double clicking on it? Otherwise some VBA to do your custom requirement.

HTH

 

[jslee]

Gah, sorry. I was so focused on describing the problem I forgot about the basics.

I'm on Win7 (Enterprise), running Excel 2007. Here's the pivot table from some sample data I made up to illustrate my question:

Row Labels	Sum of Amount
1/1/2012	$3,500.00
Customer B	$600.00
Customer C	$1,400.00
Customer D	$400.00
Customer E	$700.00
Customer F	$400.00
1/2/2012	$2,500.00
Customer A	$400.00
Customer B	$400.00
Customer C	$200.00
Customer D	$400.00
Customer E	$1,100.00
1/3/2012	$32,300.00
Customer A	$6,000.00
Customer B	$4,200.00
Customer C	$6,400.00
Customer D	$4,700.00
Customer E	$10,600.00
Customer F	$400.00
1/4/2012	$4,600.00
Customer A	$800.00
Customer C	$800.00
Customer D	$1,200.00
Customer E	$1,800.00
Grand Total	$42,900.00

<tbody>
</tbody>

So, now what I want is the following: for each of the days, I filter to see how many customers made purchases summing to over $1000. So I right-click on any of the Customer cells and hit Filter --> Value Filters, then do a filter for greater than or equal to $1000 on Sum of Amount, which gives me:

Row Labels	Sum of Amount
1/1/2012	$1,400.00
Customer C	$1,400.00
1/2/2012	$1,100.00
Customer E	$1,100.00
1/3/2012	$31,900.00
Customer A	$6,000.00
Customer B	$4,200.00
Customer C	$6,400.00
Customer D	$4,700.00
Customer E	$10,600.00
1/4/2012	$3,000.00
Customer D	$1,200.00
Customer E	$1,800.00
Grand Total	$37,400.00

<tbody>
</tbody>

Great, that's exactly what I wanted. But now I want all the drill-down details. If I double click, for example, on the first entry (Customer C's $1400 amount), I get a list of all the transactions conducted by C on 1/1/12, which total up to $1400.

But what I really want is the drill down details for all of the transaction on this filtered table. So I'd like to be able to double-click the Grand Total amount of $37,400 to give me all the transactions that sum to $37,400. These transactions should only be those that came from customers who spent more than $1000 total in one day. When I double-click that number, though, it just outputs the entire, unfiltered data set (that sums to $42,900). Is there any simple way to do this?

I tried putting Customer up above Date in the row label order, but it gave me the same results.

Sorry if I pasted in the tables incorrectly; I hope my question is clear.

 

[Fazza]

I haven't tested this, btw. It sseems to me that the solution would be simpler if the only data in the pivot table is for customers whose daily total exceed $1,000.

One way - not what I'd prefer - would be with an extra field on the source data. So a new field "ExceedsMinimum" with a GETPIVOTDATA formula that pulls from the pivot table the daily sum of the record's customer & checks whether or not it exceeds 1,000. So something like =GETPIVOTDATA(whatever)>1000. This should return TRUE for the customers whose data you want to see, and FALSE for the others. Now make another pivot table and have the new field as a page field and set it to TRUE to only show the customers of interest. Now there are two pivot tables which must both be refreshed (in the correct order) to get the result.

Another way is to not add the new field and keep it within one pivot table. This uses SQL to filter the dataset between the source and the pivot table. To set it up I'll explain one (manual) way. Give the source data a normal (non-dynamic) defined name, say "mydata". Save the file. From a new file, start the pivot table wizard ALT-D-P and choose the external data option. Follow the wizard, get data, Excel files, etc. Continue to the end and then take the option to edit in MS Query. Via the SQL button edit the SQL to suit. Hit the open door icon to exit MS Query & complete the pivot table. The resultant worksheet can be moved into the source data file if you like. (Untested) SQL would be like below.

HTH

SELECT A.Customer, A.Date, A.Amount
FROM mydata A
GROUP BY A.Customer, A.Date
HAVING SUM(A.Amount) > 1000

 

[jslee]

Thanks for the help, Fazza; I really appreciate it.

Using the GETPIVTODATA function seems to work, but unfortunately with the amount of data I have it's not really practical (it takes too long for the results to calculate/populate).

I don't have any experience with using SQL, so I had some trouble using your preferred method. I'll play around with it a little more to see if I can figure it out.

Fortunately, I solved my problem by thinking about what I needed in a different way. The original reason why I wanted that set of filtered data all in one sheet was that I could slice and dice it some more to do some analysis. But then I realized that instead of getting that separate sheet, I could just do the slicing and dicing I needed directly in the original pivot table itself.

I still wish there was a way for the "show details" function to work for a set of value-filtered data, but I think this method should suit me well.

Thanks again for the assistance!

 

[Fazza]