excelfiltergroup-bypivot-table

how to use inequality filter in pivotby formula on aggregated values, not table data


With pivot tables, there appears to be no way to use functions in the filters in the fields listings. The ability to filter values in the pivot table itself is not something that grabs your attention that there is an applied filter.

enter image description here

It is also not possible to do this filtering from a slicer. Hence I am trying to do this with the function pivotby (or groupby). The filters in the formula however only work on the original data and not the aggregated results. Is it possible to get this inside the pivotby formula or do have to filter outside of the pivotby formula?

=PIVOTBY(Load_info[Carrier],,Load_info[Load Num],COUNTA,,0,,,,(Load_info[Actual Mode]=C15)*(Load_info[Date Created]>C16)*(Load_info[Date Created]<D16))

The two filters I have inside are easily handled by slicer and timeline in a normal pivot table. However a pivot table cannot use a specific cell reference, nor am I able to do a table for using in a slicer connected to the pivot table for anything with Count number greater than 10.

This table:

Load Num Carrier
L006772 Central Transport
L006783 Central Transport
L006806 Saia Motor Freight
L006812 Central Transport
L006836 Pitt Ohio
L006837 Pitt Ohio
L006845 Central Transport
L006855 TForce Freight
L006871 Central Transport
L006873 Central Transport
L006899 FedEx Freight Economy
L006907 Central Transport
L006944 TForce Freight
L006951 Central Transport
L006977 FedEx Freight Economy
L007004 Central Transport
L007009 Pitt Ohio
L007012 Pitt Ohio
L007020 Central Transport
L007035 Central Transport
L007036 Central Transport
L007045 Central Transport
L007063 Pitt Ohio
L007091 Central Transport
L007135 TForce Freight
L007203 Central Transport
L007204 Central Transport
L007210 Central Transport
L007303 Central Transport
L007304 Pitt Ohio
L007316 FedEx Freight Economy
L007322 Pitt Ohio
L007331 Central Transport
L007383 Central Transport
L007423 Pitt Ohio
L007424 Central Transport
L007429 Pitt Ohio
L007439 Central Transport
L007441 Salson Logistics INC
L007444 FedEx Freight Economy
L007447 Salson Logistics INC
L007454 Central Transport
L007472 Central Transport
L007482 Central Transport
L007501 Central Transport
L007515 Saia Motor Freight
L007520 Central Transport
L007526 Central Transport
L007532 Central Transport
L007550 Central Transport
L007582 Central Transport
L007602 Central Transport
L007604 Saia Motor Freight
L007605 Central Transport
L007636 Central Transport
L007639 Central Transport
L007662 Central Transport
L007663 Pitt Ohio
L007665 TForce Freight
L007676 Central Transport
L007694 Pitt Ohio
L007707 Central Transport
L007713 Central Transport
L007714 Pitt Ohio
L007720 Central Transport
L007729 TForce Freight
L007734 Central Transport
L007735 Central Transport
L007743 Central Transport
L007744 Central Transport
L007748 Central Transport
L007758 Pitt Ohio
L007780 Central Transport
L007782 Central Transport
L007788 Pitt Ohio
L007791 Central Transport
L007803 Central Transport
L007811 Central Transport
L007812 Central Transport
L007813 Pitt Ohio
L007837 Saia Motor Freight
L007852 Central Transport
L007861 Central Transport
L007880 TForce Freight
L007885 FedEx Freight Priority
L007888 Central Transport
L007906 Central Transport
L007929 Salson Logistics INC
L007937 A. Duie PYLE
L007993 Salson Logistics INC
L007998 Central Transport
L008017 XPO Logistics
L008023 TForce Freight
L008032 Salson Logistics INC
L008043 Salson Logistics INC
L008049 Salson Logistics INC
L008082 Salson Logistics INC
L008104 Central Transport
L008121 Central Transport
L008122 TForce Freight
L008137 Central Transport
L008159 FedEx Freight Economy
L008165 Pitt Ohio
L008166 Salson Logistics INC
L008194 Central Transport
L008202 Salson Logistics INC
L008231 Saia Motor Freight
L008234 Central Transport
L008248 Central Transport
L008260 Central Transport
L008263 FedEx Freight Economy
L008289 Central Transport
L008291 TForce Freight
L008308 Pitt Ohio
L008390 Central Transport
L008418 Central Transport
L008432 TForce Freight
L008451 TForce Freight
L008472 Central Transport
L008474 Pitt Ohio
L008478 Central Transport
L008494 Central Transport
L008515 TForce Freight
L008520 Central Transport
L008563 A. Duie PYLE
L008593 XPO Logistics
L008598 Pitt Ohio
L008611 Central Transport
L008617 Central Transport
L008646 Central Transport
L008657 Salson Logistics INC
L008666 Salson Logistics INC
L008685 Central Transport
L008691 TForce Freight
L008695 Central Transport
L008699 Central Transport
L008738 Salson Logistics INC
L008744 Central Transport
L008748 TForce Freight
L008799 Pitt Ohio
L008803 Saia Motor Freight
L008805 Central Transport
L008807 Central Transport
L008864 Salson Logistics INC
L008871 Pitt Ohio
L008890 Central Transport
L008905 TForce Freight
L008913 Central Transport
L008942 Salson Logistics INC
L008977 XPO Logistics
L008984 FedEx Freight Economy
L009020 Central Transport
L009040 Central Transport
L009138 TForce Freight
L009153 Central Transport
L009165 TForce Freight
L009184 FedEx Freight Priority
L009201 TForce Freight
L009225 Central Transport
L009232 Central Transport
L009283 Central Transport
L009295 Central Transport
L009343 FedEx Freight Economy
L009346 Pitt Ohio
L009370 FedEx Freight Priority
L009375 Central Transport
L009383 Salson Logistics INC
L009392 Central Transport
L009395 TForce Freight
L009430 Central Transport
L009442 Central Transport
L009443 Central Transport
L009446 Central Transport
L009458 Central Transport
L009459 Salson Logistics INC
L009468 Central Transport
L009470 Central Transport
L009476 Central Transport
L009480 Central Transport
L009488 Central Transport
L009511 A. Duie PYLE
L009524 Pitt Ohio
L009538 Central Transport
L009572 Central Transport
L009576 Central Transport
L009597 Central Transport
L009598 TForce Freight
L009601 Central Transport
L009605 XPO Logistics
L009618 Pitt Ohio
L009620 Central Transport
L009627 Central Transport
L009652 Pitt Ohio
L009654 Pitt Ohio
L009655 Saia Motor Freight
L009663 Central Transport
L009672 Salson Logistics INC
L009681 Central Transport
L009692 Salson Logistics INC
L009742 Central Transport
L009746 FedEx Freight Economy
L009781 Central Transport
L009786 Central Transport
L009804 Central Transport
L009805 Central Transport
L009806 Central Transport
L009808 Central Transport
L009836 Central Transport
L009871 TForce Freight
L009881 A. Duie PYLE
L009897 Central Transport
L009905 Central Transport
L009926 Central Transport
L009928 FedEx Freight Economy
L009963 Central Transport
L010005 Central Transport
L010016 TForce Freight
L010028 Central Transport
L010034 Salson Logistics INC
L010045 Salson Logistics INC
L010046 TForce Freight
L010049 Central Transport
L010057 Central Transport
L010072 A. Duie PYLE
L010088 Central Transport
L010094 Central Transport
L010103 A. Duie PYLE
L010124 Central Transport
L010132 Central Transport
L010141 Central Transport
L010154 Central Transport
L010164 Pitt Ohio
L010189 Salson Logistics INC
L010213 Pitt Ohio
L010232 Central Transport
L010244 Central Transport
L010245 Central Transport
L010256 Central Transport
L010261 Central Transport
L010295 Salson Logistics INC
L010311 Central Transport
L010316 Central Transport
L010353 Central Transport
L010357 TForce Freight
L010417 Central Transport
L010473 Central Transport
L010475 Central Transport

Pivot table gives:

enter image description here

With returned # of rows being changeable. Looking to use cell reference (value 10) to filter out the pivot table to another listing of only those counts equal to and above.

enter image description here


Solution

  • You can use the GROUPBY function with the filter argument: The minimum count value is in F2 in this example, and the table is Named Table6. Change as needed.

    *Note that I am uncertain if any particular logic need be applied to the Load Num column. But it could likely be added if needed.

    =GROUPBY(
        Table6[Carrier],
        Table6[Carrier],
        COUNTA,
        0,
        0,
        ,
        BYROW(
            Table6[Carrier],
            LAMBDA(arr, COUNTIF(Table6[Carrier], arr))
        ) >= F2
    )
    

    Output from your data with minimum count set to 10

    enter image description here

    EDIT:
    As an alternative, you could put the filter OUTSIDE the GROUPBY function
    The formula above demonstrates use of the filter_array argument of the GROUPBY function

    =LET(
        t, GROUPBY(Table6[Carrier], Table6[Carrier], COUNTA, 0, 0),
        FILTER(t, CHOOSECOLS(t, 2) >= F2)
    )