8 Replies Latest reply on Aug 24, 2016 1:43 PM by Joe Oppelt

# Incorrect % Calculation when  filter is selected

Hi ,

I have a requirement to show the percentage  of Regulation based on the total number of records and this coulmn will always two values like SOX and MON.when i have selected both values in the filter result of percentage is showing correct whenever i have selected a single value out of Two values (SOX and Mon) then it is showing 100% .

Below is the example :

Total Records are  : 37

SOX is  : 21 , percentage of total is - 57%

MON is : 16 , Percentage of total is : 43%

if i have selected both the values, result  of  % is showing correct  and if i am selecting the either of the value the result of % is showing 100% which is incorrect , instead of 57% or 43% respective selection criteria.

Thanks a lot for your help .

Message was edited by: POTHURAJU BOLEM

• ###### 1. Re: Incorrect % Calculation when  filter is selected

Right.

The quick filter eliminates records from the table.

What you need to do is filter using a table calc.  That only filters out what gets displayed and leaves the underlying table intact.

If you post a sample workbook I can show you how to do it if you don't already know how.

• ###### 2. Re: Incorrect % Calculation when  filter is selected

Hi Joe,

I have posted the sample workbook, can you please guide the steps how we can achieve this by using a table calc.

Thanks,

Raju

• ###### 3. Re: Incorrect % Calculation when  filter is selected

Hi,

I may be late to the party.

But you can use below calculation for correct percentages.

{ FIXED [Regulation]:SUM([Number of Records])/SUM({FIXED :SUM([Number of Records])})}

This should work, no matter what you regulation filter is.

Let me know if it doesnt work.

Thanks,

Aseem

• ###### 4. Re: Incorrect % Calculation when  filter is selected

In the attached, Sheet 4 uses Aseem's solution.  Sheet 3 uses a table calc as I suggested.  Both create the same result.

• ###### 5. Re: Incorrect % Calculation when  filter is selected

Hi Joe,

Thanks a lot for your quick help !!.

It works well now , but I could not able to fix the axis ranges  due to this work sheet showing as bit gap end of the right side of the Bar.

if i have selected to  fixed start and end axis ranges then it is  Bar chart shows a large gap , if I am selecting as uniform then also it still showing some little bit gap.

is there any way we can fix this , could you please suggest on this .

Thanks,

Raju

• ###### 6. Re: Incorrect % Calculation when  filter is selected

I see what you mean, but I don't see a way to change that.  Especially the little extra that Tableau wants to tack onto the end of the chart.

But I see that the behavior in Sheet 4 is different from Sheet 3 when you select only one Regulation.  Choose Automatic or Uniform for both, and you'll see what I mean.  I don't know which serves you better, but each method works a little differently on the display.

So in the attached, in Dashboard 1, I did a hack to display the viz the way I think you are looking to display it.  I used the Sheet 4 method (Aseem's solution).  I made a copy of Sheet 4 (called it Sheet 5) and placed that on the dashboard.  I floated the actual Sheet 4 on top of it, without a title and without the axis tick marks.  That allows I stretched Sheet 4 to fill out that extra little bit you didn't want to see.  And Sheet 5 Title and axis are still visible. To the user is all just looks like one viz.

Certainly this is a hack, but it gets the job done.

• ###### 7. Re: Incorrect % Calculation when  filter is selected

Hi Joe,

If I am arranging multiple sheets  in the dashboard 1 , regulation chart display looks like  some inconsistency due to the gap between at the end of the right side of the Blue Bar and layout container  .

is there a way to fix this , i tried to change axis ranges from automatic to uniform ,still it is showing the gap.

Thanks,

Raju

• ###### 8. Re: Incorrect % Calculation when  filter is selected

Did you look at the example I uploaded in my last reply?

I don't see a way to eliminate that directly in the sheet by playing with the axis itself.  I can only suggest the workaround I hacked up in the example I gave.  And that workaround is going to require floating sheets, not placing them in a container.