4 Replies Latest reply on Oct 25, 2016 10:46 AM by Joe Oppelt

# Keep Overall Total denominator the same when filtering

I am trying to show a percent of "overall total" one-liner in my cross-tab, so I can use it for a chart.  What I want is for this....

2000     2001     2002

Y            30        35          15

N            10        40          12

Total       40        75          27

... to show as this...

2000     2001     2002

Y          75%      46%       55%

The problem I face is when I filter out the N row the Overall Total count reduces making my Y row show up as 100%.

• ###### 1. Re: Keep Overall Total denominator the same when filtering

Use a table calc to filter, rather than a quick filter.

The quick filter eliminates rows from the underlying table, and that will change your totals.

A table calc in a filter controls what is displayed, but does not change the underlying table.

Attached is an old example (using dates, but the principle is the same) for using LOOKUP as the calc filter.

1 of 1 people found this helpful
• ###### 2. Re: Keep Overall Total denominator the same when filtering

Joe - appreciate the answer and example.  However, I didn't see a LOOKUP performed in your attached file.  Not sure if I am just overlooking it.

I got an answer from another source.  Create two calculated measures with the following logic based on the Y or N value coming from a field titled [Renewal]...

Calculated Measure #1: [Record Value]

1 (NOTE: always equal to 1)

Calculated Measure #2: [

SUM(If [Renewal] = "Y" Then 1 End)  /  SUM(Number of Records)

I double checked my numbers and this approach worked perfect for me.  Thank you for the help!

• ###### 4. Re: Keep Overall Total denominator the same when filtering

Colin White wrote:

Joe - appreciate the answer and example.  However, I didn't see a LOOKUP performed in your attached file.  Not sure if I am just overlooking it.

See the [view dates] calc in the filter.

The line is a running sum.  If you turn off Jan, Feb, Mar, for example, you will see that the running sum for April remains at \$83K.

It's a neat technique.

But you have a solution, so no need to switch tracks now.