Pivot table cannot filter zero values

2019-08-20 22:58发布

I have data that collects "snapshots" of a Warehouse.
The data has three columns: SnapshotDate, Item, OnHand

I have defined these measures on data:

Measure On Hand Current:

On Hand Current:= 
    SUM ( Inventory[OnHand] ) + 0

Measure On Hand Previous:

On Hand Previous:=
VAR DateCurrent = 
    IF ( 
        HASONEVALUE ( 'Date Dimension'[SnapshotDate] ),
        VALUES ( 'Date Dimension'[SnapshotDate] ),
        BLANK()
    )
VAR DatePrevious = 
    CALCULATE ( 
        MAX ( 'Date Dimension'[SnapshotDate] ),
        FILTER ( 
            ALL ( 'Date Dimension' ),
            'Date Dimension'[SnapshotDate] < DateCurrent
        )
    )
RETURN
    CALCULATE ( 
        [On Hand Current], 
        FILTER ( 
            ALL ( 'Date Dimension'[SnapshotDate] ),
            'Date Dimension'[SnapshotDate] = DatePrevious
        )
    ) + 0

Measure Delta:

Delta:=
    [On Hand Current] - [On Hand Previous]

Preamble: in my “real” situation, I have nearly 99% of items that are not handled from a date to the next. Since I am mostly interested to measure the Delta OnHand from a date to the next, I want to hide Items that have no differences. For example (see screenshot below), I’d like items K and X to disappear from the pivot table.

enter image description here

To do this, I uncheck the first date (01/04/2019) from the “Columns Labels” and I add a filter on the OnHandCurrent using the Excel filter “Greater than…”.

enter image description here

This is the result:
enter image description here

The row with item K doesn’t disappear!

Link to the Excel file with sample data.

标签: excel pivot
0条回答
登录 后发表回答