Filter a pivot table based on the values in specific columns

Question

I have a pivot table where there are multiple column labels:

group subgroup subsubgroup status

The last label, status, has only two possible values, ACTIVE and DORMANT. I need to show only those rows where the value for ACTIVE is missing, but the value for DORMANT is not.

I know this is possible by partially aggregating the data (so my source data won't have a single COUNT field, but will have two: ACTIVE_COUNT and DORMANT_COUNT) and prefiltering it before constructing the pivot, but I cannot modify the shape of the source data for the pivot table in this particular case.

An example of what I need:

ROW G1 G2 G3 STAT CNT
---------------------
V11 AA BB C1 ACTV 100
V11 AA BB C1 DORM 200
V11 AA BB C2 ACTV 400
V11 AA BB C3 DORM 800
V11 AA BB C4 DORM 900
V21 AA BB C1 ACTV 110
V21 AA BB C1 DORM 220
V21 AA BB C2 ACTV 440
V21 AA BB C3 DORM 880
V21 AA BB C3 DORM 990
V21 AA BB C4 ACTV   1

Has to be transformed into:

.   AA
.   BB
.   C3   C4
.   DORM DORM
-------------
V11 800  900
V21 990

I.e., C1 is not shown because there's a matching row with ACTV>0, C2 is not shown because there's no DORM, V21 x C4 DORM is not shown because there's a matching row with ACTV>0, but both V11 x C4 DORM and V21 x C3 DORM are.

Answer 1

I believe this beyond the capabilities of just a Pivot Table, as you are trying to match across rows.

I would pre-calculate a new "Row Status" column using the Power Query Add-In. It can Merge data together so you can build one Query filtered to just the rows with ACTV>0, then Merge it with the original table to produce a new "Row Status" column that you can filter on.

https://support.office.com/en-us/article/Merge-queries-fd157620-5470-4c0f-b132-7ca2616d17f9?ui=en-US&rs=en-US&ad=US

Power Query has lots of other data transformation functions that you might find useful.