Copper Contributor

Jun 19, 2022

Solved

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

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.

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

First Name Last Name Trip Price Year

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	54.54	2021-03-05
Ayana	Grant	Canada	500.65	2021-04-09
Ayana	Grant	Canada	256.23	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

Formulas and Functions

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"

filter annual trips by price.xlsx25 KB

14 Replies

Riny_van_Eekelen

Platinum Contributor

Jun 19, 2022

zander140

In the initial pivot table, right-click on the row labels containing the First Name. Choose Filter, then Value Filters.... Now set the filter to Sum of Amount, is greater than and type 5000 in the empty box and press OK.

zander140

Copper Contributor

Jun 19, 2022

Riny_van_Eekelen

Thank you for your reply. I should have mentioned that I already tried doing that 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.

Dataset:

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

SergeiBaklan

Diamond Contributor

Jun 19, 2022

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"

filter annual trips by price.xlsx25 KB

Forum Discussion

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

14 Replies

Resources