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.
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:
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.
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
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)
)