4 Replies Latest reply on Jul 21, 2015 1:03 AM by Bora Beran

    indicate above/equal/below to overall percentage?

    Alissa Swartz

      I've got a series of worksheets that show the % response to a set of survey questions (in the image below, each "training" = a response option to the survey question). I also have a set of quick filters added to the worksheet.

       

      tableau high low question unfiltered.JPG

       

      I'd like to add an indicator to each row that shows whether, when filtered, the percentage response is above, equal to, or below the overall percentage response. For example, if I filter the question by "partial v. complete survey" or "engagement ladder", I'd like an arrow or different color dot or something to show up, for an easy visual comparison to the overall percentage.

       

      For example, I filtered this worksheet by Engagement Ladder = 1 and added arrows/equal signs via Paint...

       

      tableau high low question.JPG

       

      How can I achieve this in tableau?

        • 1. Re: indicate above/equal/below to overall percentage?
          Bora Beran

          This could be done with an LOD calc or a table calc. You want the calc to return a discrete result like equal, less than or great than and you can drag that calculation on the shape shelf and select up arrow for one, down arrow for one etc. Ideally on a secondary axis using the same field that is on x axis  but with some padding (e.g. add a fixed number to the axis value) so it doesn't overlap with the actual mark but instead renders next to it.

           

          To get a more detailed response from the forums, I am afraid you need to provide more details. E.g. attach a sample workbook or share the structure of the input files and what you have in the sheet (can't tell from the dashboard what dimensions are on what shelf in the sheets)

          • 2. Re: indicate above/equal/below to overall percentage?
            Alissa Swartz

            Ok - here's a screen shot of the table (I changed the table type, but hte data is the same).

             

            tableau high low question non anon.JPG

             

            Not sure what you need to know about the structure of the input files. It's made up of two surveys (2014 and 2015), same questions. Each respondent has a respondent ID with a 1 = yes and 0 = no if they selected the question response option. So, what you see above is the % of people who said that they had participated in one of the following leadership development programs. "None of the above" is hidden.

            • 3. Re: indicate above/equal/below to overall percentage?
              Alissa Swartz

              Bumping for the morning crowd.... Bora Beran, any ideas?

              • 4. Re: indicate above/equal/below to overall percentage?
                Bora Beran

                I can take a guess based on the screenshot.

                 

                So I am assuming what you mean by overall percentage is count of yes divided by count of all respondents for a question.

                 

                That would be something like

                 

                {fixed [Question Text] : SUM(Measure that gives 1 for yes)/SUM(# respondents)}

                 

                I have a detailed blog post about LOD calcs here if you want to know the details What’s new in Tableau 9.0? Part 2 – Level of Detail Expressions « Bora Beran but in short fixed keyword means the filters will be ignored in this calculation unless they are context filters, data source filters or extract filters. So the filters you apply won't change the calculation of the overall value.

                 

                If you want to get a different value for each year or any other dimension, you can add to the formula

                 

                {fixed [Question Text], [Date Field Here]: SUM(Measure that gives 1 for yes)/SUM(# respondents)}

                 

                In 9.0 you have to create  calculated field that does YEAR(Date) and use that one in the calculation. Typing YEAR(Date) into the fixed expression above won't work.

                 

                Without this extra date field the calculation will give the ratio across multiple years.

                 

                You can compare your post filter calculation with this one inside an IF block

                 

                IF [the calc I listed above] > [your calc] then 'Below average'

                Elseif..

                END

                 

                Then select mark type to be shape and drag this onto the shape shelf which will give you different icons for different cases covered in IF..THEN..ELSE..

                 

                If you don't want to change marks you have in the viz, you can put this on the secondary axis.

                 

                Without a workbook, this would be my best guess.