How to filter by subtotals if over $5000 - Pivot Table/VBA?

zander140 

We may play with Power Query. For such model

first prepare the source table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
            {"Year", type date},
            {"Price", Currency.Type},
            {"Trip", type text}
        }         ),
    #"Merged Columns" = Table.CombineColumns(
        #"Changed Type",
        {"First Name", "Last Name"},
        Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Name"),
    #"Extracted Year" = Table.TransformColumns(
        #"Merged Columns",
        {{"Year", Date.Year, Int64.Type}})
in
    #"Extracted Year"

Next generate list of people who spent more than 5000 annually. We ignore years with no trips.

let
    Source = Table,
    Bar = Excel.CurrentWorkbook(){[Name="PriceBar"]}[Content]{0}[Column1],

    #"Grouped Rows" = Table.Group(
        Source,
        {"Name", "Year"},
        { {"Total", each List.Sum([Price]), type nullable number}}),
    #"Grouped Rows1" = Table.Group(
        #"Grouped Rows",
        {"Name"}, {{"Data", each List.Min( _[Total] ) > Bar }}),
    #"Filtered Rows" = Table.SelectRows(
        #"Grouped Rows1",
        each ([Data] = true)),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Filtered Rows",{"Name"}),
    Name = #"Removed Other Columns"[Name]
in
    Name

Finally filter source table by these names and land query to the grid as PivotTable

let
    Source = Table,
    #"Filtered Rows" = Table.SelectRows(
        Source,
        each List.Contains(#"Names Filtered", [Name] ))
in
    #"Filtered Rows"