Filter pivot table if any month value is not zero

[cr731]

I have a table with a column of dates and a column of values, which should all be zero (variances).

When I pivot this and put the dates in the columns, I want to filter the table so that all rows with a variance in any month will remain. But when I use a value filter, it just filters for the summed value of all months, so that if January is -10 and February is +10, it would net to zero and not be captured.

Is there any way to do this, either with basic pivot table functionality or using a calculated measure (DAX)?

Thanks

 

[B___P]

Please paste an example. Did you try to group elements in pivot table?

 

[cr731]

Sorry if this is not the right place to ask this but, is there any easy way to paste an example such as a screenshot? Or do I have to manually type in table values?

 

[cr731]

Here is an example of what the table may look like.

Name	Period (Data Type is Date)	Value
A	Jan 2016	-5
A	Feb 2016	5
B	Mar 2016	3
C	Apr 2016	-7

<tbody>
</tbody>

If this is pivoted where

Rows: Name column
Columns: Period column
Values: Value column

Then you would get something like

	Jan 2016	Feb 2016	Mar 2016	Apr 2016	Grand Total
A	-5	5	0	0	0
B	0	0	3	0	3
C	0	0	0	-7	-7

<tbody>
</tbody>

Now, if I apply a Values filter to the row labels and say Does Not Equal it would just give me the option of "Value" does not equal and I enter 0. This basically is filtering based on the Grand Total column, so A would get filtered out. However, since Jan and Feb have non-zero values, I would want to see them.

Short of converting all values to absolutes, is there a way to filter this table to basically say if any value in any of the columns is not 0, then keep that row?

 

[B___P]

At the beginning of this forum there is an announcement about Recommended Add-ins and Links
to paste a screenshot MrExcel HTML Maker is usefull.

I asked an example to understand variation over months: January -10 and February 10 -> skip, but what if March -5? Should February reappear?


Posted after your answer
</posted>

 

[B___P]

Try to add =COUNT(B5:E5) at the right of Grand total. If you keep empty cells as in pivot this shows evidence of variation and a filter on it will preserve evidence even if balance is 0

 

[citizenbh]

1. From PowerPivot new PivotTable
2. In ROWS - Name, In COLUMNS - Period, In VALUES - Values
3. Disable in PivotTable Options /Total & Filters/ Show grand totals for columns

 

[Ozeroth]

A couple of ideas I can think of:

1. Turn zeros into blanks, then the default "empty row removal" feature of pivot tables will hide those rows.
   You can do this using two measures like:
   

   Value Original := SUM( YourTable[Value] )
   Value with zeros hidden := IF ( [Value Original] = 0, BLANK(), [Value Original] )

   Then put [Value with zeros hidden] in the Value field of the pivot table.
2. Create a measure which counts the number of periods for which [Value Original] is nonzero, and use this in a Value Filter. Assuming [Value Original] is defined as above:
   

   Num Periods with Nonzero Value :=
   COUNTX(
       VALUES( YourTable[Period] ),
       IF ( [Value Original] <> 0, 1 )
   )

   Don't show this measure on the pivot table, but filter Name using a Value Filter so that "Num Periods with Nonzero Value > 0".

Owen

 

[cr731]

Thanks for all the suggestions.

Regarding those calculated fields in Ozeroth's post, will that still work if the net total is zero? For instance, if May is -10 and June is +10, will the COUNTX formula still see that as zero and filter it out? I need to see all rows where any individual month is non-zero.

 

[Ozeroth]

Yes they will.
I tested filtering on the COUNTX measure myself and it kept rows with nonzero months but zero totals.
Since it iterates over months, it will count nonzero months regardless of grand total.
Try it yourself and confirm though : )