Filtering Summed Values

[kellwood]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD] Date

[/TD]
[TD]Name
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]June 1
[/TD]
[TD]Bob
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]June 1
[/TD]
[TD]Bob
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]June 1
[/TD]
[TD]Doug
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]June 2
[/TD]
[TD]Bob
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]June 2
[/TD]
[TD]Doug
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]June 2
[/TD]
[TD]Doug
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]June 3
[/TD]
[TD]Bob
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]June 3
[/TD]
[TD]Bob
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]June 3
[/TD]
[TD]Doug
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]June 3
[/TD]
[TD]Doug
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

I'd like to pivot the above data with Names in the rows, Dates in the columns, and the sum of the amounts for each person each day in the values. I know how to do this. My problem is I would like to filter the values to only show days that are greater than 10. Example Doug's sum of 5 on June 1 would be filtered out of the table (display as blank cell). Bob's sum on June 1 would be displayed as 11 because the sum is greater than 10.

Is this possible? When I attempt it, my summed values filter based on the Grand Total, which doesn't help me.

 

[sandy666]

maybe

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Date[/td][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Amount[/td][td][/td][td=bgcolor:#70AD47]Date[/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Sum Amount[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/06/2019​

[/td][td=bgcolor:#DDEBF7]Bob[/td][td=bgcolor:#DDEBF7]

5​

[/td][td][/td][td=bgcolor:#E2EFDA]

01/06/2019​

[/td][td=bgcolor:#E2EFDA]Bob[/td][td=bgcolor:#E2EFDA]

11​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

01/06/2019​

[/td][td]Bob[/td][td]

6​

[/td][td][/td][td]

02/06/2019​

[/td][td]Doug[/td][td]

11​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

01/06/2019​

[/td][td=bgcolor:#DDEBF7]Doug[/td][td=bgcolor:#DDEBF7]

5​

[/td][td][/td][td=bgcolor:#E2EFDA]

03/06/2019​

[/td][td=bgcolor:#E2EFDA]Doug[/td][td=bgcolor:#E2EFDA]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

02/06/2019​

[/td][td]Bob[/td][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

02/06/2019​

[/td][td=bgcolor:#DDEBF7]Doug[/td][td=bgcolor:#DDEBF7]

7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

02/06/2019​

[/td][td]Doug[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

03/06/2019​

[/td][td=bgcolor:#DDEBF7]Bob[/td][td=bgcolor:#DDEBF7]

5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

03/06/2019​

[/td][td]Bob[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

03/06/2019​

[/td][td=bgcolor:#DDEBF7]Doug[/td][td=bgcolor:#DDEBF7]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

03/06/2019​

[/td][td]Doug[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

with PowerQuery (Get&Transform)

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Amount", Int64.Type}}),
    Group = Table.Group(Type, {"Date", "Name"}, {{"Sum Amount", each List.Sum([Amount]), type number}}),
    Filter = Table.SelectRows(Group, each [Sum Amount] > 10)
in
    Filter[/SIZE]

 

[kellwood]

Thanks Sandy. I am not familiar with PowerQuery....but I'll certainly check it out now. Any way do get the same result in a pivot table?

 

[sandy666]

sure

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Date[/td][td=bgcolor:#DDEBF7]Name[/td][td=bgcolor:#DDEBF7]Sum of Amount[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

01/06/2019​

[/td][td]Bob[/td][td]

11​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

02/06/2019​

[/td][td]Doug[/td][td]

11​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

03/06/2019​

[/td][td]Doug[/td][td]

12​

[/td][/tr]
[/table]

 

[kellwood]

Thanks again Sandy. Sorry for my ignorance, but I am still messing it up somehow. When I drop the Dates and Names into the Rows field like you have shown my pivot table displays differently than yours....my Names are sub rows under the Dates...not another column like yours is shown.

 

[Fazza]

hi,

The simple filtering explained by Sandy is great. Hopefully that is sufficient for your requirements.

Below are some other ideas FYI.

One way with an existing pivot table is to add another field to the source data. Say "ShowIt".
Populate with a formula that links to the pivot table, something like
=GETPIVOTDATA(references to get the total you want to check against)>10
Now make a second pivot table & put the ShowIt field as a page field to filter for TRUE.
Refresh the first then the second pivot table if data changes.


More complex is if you want the result directly in a pivot table, and without adding an extra field to the data. This can be done by creating a dataset that already has the filtering.
Such as defining the dataset by, untested

SELECT A.[Date], A.Name, A.Amount
FROM YourDataName A
GROUP BY A.[Date], A.Name
HAVING SUM(A.Amount) > 10

This works in all versions of pivot tables, BTW. Detailed steps follow, or search for examples via Google.
When the source data has simple defined name "YourDataName". Such as select the data then CTRL-F3 & define the named range, or via the name box at top LHS.
Field name "Date" may be a problem - hopefully enclosing in brackets avoids issues.
Having saved the data file, safest for earlier Excel versions is to then have a new file when creating the pivot table.
So, CTRL-N for a new Excel file
ALT-D-P to start the pivot table wizard
external data source at the first stop then follow the wizard to the end choosing the option then to edit in MS Query.
Via the SQL button define the dataset as above, OK to enter & OK again if you get a message. See the results set.
Via the open door button exit MS Query & complete the pivot table.
Having created the pivot table on a worksheet, you can if you want move the entire worksheet to the same workbook as the data.


regards, Fazza

 

[sandy666]

select any cell in PivotTable then:

 

[kellwood]

Thanks Sandy, that works! Thank you too Fazza! Last question...how do I close the thread or marked solved? (I'm new)

 

[sandy666]

just say thanks , click bottom left buttons Thanks/Like in appropriate post/s - that's all