4 Replies Latest reply on Nov 2, 2016 7:11 PM by Mike Loveless

# Filter by Table Calculation, but show the other measures.

Hi all,

I'm attaching a sample replicating what I'm attempting to do.  I had a table calc showing the difference between one month to the next.  I turned this table calc into a calculated field.  I then want to only look at the results that have negative values for the difference, but I still want to see the total Sum of Sales for all the months so I can see all the sales that are resulting in the negative change.  Currently it is hiding the results for the totals based on the filter of the table calc.  I'm hoping I'm missing something simple as this seems like it should be easy to accomplish.  Any thoughts are appreciated.

Thanks,

Mike

• ###### 1. Re: Filter by Table Calculation, but show the other measures.

Mike,

if ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1) < 0 then ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1) end

So , if negative show the value otherwise show  null. See in the attached

Michel

• ###### 2. Re: Filter by Table Calculation, but show the other measures.

Incredibly simple which I love, but still doesn't give me the results that I'm looking for.  I'm guessing I didn't explain it well.

I want to see the total sales for all months for all the sub-categories that have a negative month to month difference, but I don't want to see the subcategories that always have a positive result.  In my real set of data I'm using over 300 rows so scrolling down doesn't really work well.

While this removes the value of the difference, it is still showing all the subcategories like "Appliances" which is always increasing.  Ideally, "Appliances" wouldn't even be visible in the returned results.  Chairs, Fasteners, Paper, and Supplies are also like this.  When it is a small data set like the example, this would work, but not when it is a large data set.

Hope that helps clarify.

• ###### 3. Re: Filter by Table Calculation, but show the other measures.

like this ?

1 of 1 people found this helpful
• ###### 4. Re: Filter by Table Calculation, but show the other measures.

That's exactly what I'm looking for!  Many thanks!