SOLVED

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

Copper Contributor

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

 

Over 5000.JPG

 

First Name Last Name    Trip           Price           Year

Esther AllanUS1806.452019-04-19
Esther AllanCanda359.262019-07-20
Esther AllanItaly5078.452022-05-04
John SmithUS2000.262021-02-02
John SmithPoland2500.452022-05-05
John SmithJapan3516.562021-05-07
John SmithJapan3500.892022-07-04
Ayana GrantCanada54.542021-03-05
Ayana GrantCanada500.652021-04-09
Ayana GrantCanada256.232021-02-25
JimmyLeeCanada455.232019-04-19
JimmyLeeCanada454.562021-05-02
JimmyLeeCanada670.212021-07-02

 

14 Replies

@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.

@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.

 

zander140_0-1655646841280.png

 

Dataset:

First Name Last NameTrip PriceYear

Esther AllanUS1806.452019-04-19
Esther AllanCanda359.262019-07-20
Esther AllanItaly5078.452022-05-04
John SmithUS2000.262021-02-02
John SmithPoland2500.452022-05-05
John SmithJapan3516.562021-05-07
John SmithJapan3500.892022-07-04
Ayana GrantCanada653.232021-03-05
Ayana GrantCanada756.232022-04-09
Ayana GrantUS4000.562021-02-25
JimmyLeeCanada455.232019-04-19
JimmyLeeCanada454.562021-05-02
JimmyLeeCanada670.212021-07-02

@zander140 Ah, I see. That would probably require a clever DAX measure. Not something I can help you with right now. Sorry!

best response confirmed by zander140 (Copper Contributor)
Solution

@zander140 

We may play with Power Query. For such model

image.png

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"

Hi Sergei, Thank you very much. I thought Esther's data would show as well since her subtotal is greater than $5000 for 2022. I think what the program is doing is, it is removing Esther from the list since for 2019 her spent was only $2165.71. It would be great if any of the year had over $5000 as a subtotal it would show those (highlighted in red). Thanks. 

zander140_0-1655657604347.png

 

@zander140 

Please see updated queries in attached file, result is

image.png

@Sergei Baklan Thank you so much, Sergei. Can this method be used for a pre-existing Excel Table? Thank you. 

@zander140 

In general yes, you need to adjust queries to actual data.

@zander140 

Another way could be with DAX measure only. Load you table and parameter to data model and use measure like

Cost :=
VAR bar =
    VALUES ( PriceBar[Bar] )
RETURN
    SUMX (
        Table1,
        IF (
            CALCULATE (
                SUM ( Table1[Price] ),
                ALLEXCEPT ( Table1, Table1[Name], Table1[Year (Year)] )
            ) > bar,
            Table1[Price],
            BLANK ()
        )
    )

Result is the same, see second PivotTable

image.png

Thank you, Sergei. Thanks to you, I learned Power Query and applied it to my other project to do the same function. Power Query is amazing. I will look into DAX as well. Thank you.

@zander140 , you are welcome, glad to help

Hi Sergei, I thought everything was fine but when I put in new data and press refresh for the dataset I adapted your model for, I am getting the following error. Can you please help resolve this. Thanks.

 

zander140_0-1655778684926.png

 

@zander140 

Perhaps you may share small sample file removing sensitive information? Your dates could be actually texts and/or format is not the same as default one for your Power Query locale. 

You may right click on the column on Source step, select column and from right click menu select

image.png

and here something like

image.png

Perhaps something else, hard to say without sample file.

Hi Sergei, I sent you a direct message. Thank you.
1 best response

Accepted Solutions
best response confirmed by zander140 (Copper Contributor)
Solution

@zander140 

We may play with Power Query. For such model

image.png

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"

View solution in original post