1 Reply Latest reply on Nov 1, 2016 1:08 PM by Joe Oppelt

    % of totals are changing with filters! - ARGH!

    John Matyasovsky

      Please find the attached workbook.  This is part of a workbook for criminal judges stats.  I have created on this workbook a page that shows the percentage of disposition types by judge.


      I do not want to show all of the judges at once.  I want to create a filter that will allow me to pick specific judges to display at one time.  The problem is when I add a filter, the calculations change and are dependant on the judges chosen.  In other words if I pick one judge, all of their disposition types and counts become 100%, not the original percentages. 


      How do I keep the percentages calculating from the whole when actively selecting/filtering on specific judges?  I understand in newer versions of Tableau there is a “FIXED” function, but it is not available in this version (8.2) which I am forced to use due to our version of server.


      Any heelp would be appreciated..,

        • 1. Re: % of totals are changing with filters! - ARGH!
          Joe Oppelt

          You want to use a table calc to filter your judges.  Not a quick calc.


          See attached.  LOOKUP() is a table calc function.


          What a table calc does when it is used as a filter is governs what rows get displayed, but it leaves the underlying table intact.


          You can see the effect of the full table remaining intact when you select only one judge.  The %ofTotal is maintained for the individual rows, but the overall GRAND TOTAL still comes out as 100%.  Tableau still sees all the rows to create that.

          You probably don't want the grand total on there.  In fact, the presence of the grand total is why you see an asterisk as a filter choice.  GRAND TOTAL is looked at by Tableau as all the judges added together.  And therefore there are multiple values of Judge Names rolled into it (and thus you get the asterisk.)


          See Sheet 11.  Same sheet, but I took grand total off.  I also changed the filter display to single select.  And I customized it to remove ALL.  Maybe you want ALL, so you don't need to remove it.  Sheet 11 is just an example of some things you can do with this.


          PS:  LOOKUP isn't the only way to do a table calc for a filter.  It's just a basic technique I use in all sorts of ways.