Jun 19 2022 07:07 AM - edited Jun 19 2022 07:10 AM
Hello everyone,
I was wondering in a Pivot Table, how can I filter/use a slicer to display all individuals who have a subtotal over $5000 per year.
I did conditional formatting to identify such individuals (highlights subtotals in red) but the pivot table would not let me isolate them to show only the individuals with subtotals exceeding $5000 per year.
Please see the picture below for a visual example. I am not sure how to attached the excel file with the pivot table but I pasted the table below. Thanks.
I also tried to right-click on the Pivot table containing the First Name and clicked on Filter, then Value Filters and sent the filter to Sum of Amount is greater than and type 5000 but it also shows if the grand total of the individual is greater than $5000 for all years instead of isolating for the subtotal of each year and checking that. Please see below. Thanks.
If you can provide VBA code or share how it would be possible via the Pivot table, that would be awesome. Thank you.
Kind regards,
Zander
Value filter results(shows if grand total for an individual is greater than $5000 instead of per year):
Dataset(since I am not able to post the Excel File):
First Name Last NameTrip PriceYear
Esther | Allan | US | 1806.45 | 2019-04-19 |
Esther | Allan | Canda | 359.26 | 2019-07-20 |
Esther | Allan | Italy | 5078.45 | 2022-05-04 |
John | Smith | US | 2000.26 | 2021-02-02 |
John | Smith | Poland | 2500.45 | 2022-05-05 |
John | Smith | Japan | 3516.56 | 2021-05-07 |
John | Smith | Japan | 3500.89 | 2022-07-04 |
Ayana | Grant | Canada | 653.23 | 2021-03-05 |
Ayana | Grant | Canada | 756.23 | 2022-04-09 |
Ayana | Grant | US | 4000.56 | 2021-02-25 |
Jimmy | Lee | Canada | 455.23 | 2019-04-19 |
Jimmy | Lee | Canada | 454.56 | 2021-05-02 |
Jimmy | Lee | Canada | 670.21 | 2021-07-02 |
Jun 19 2022 09:18 AM