4 Replies Latest reply on Oct 3, 2016 4:55 PM by Ivan Young

    Percent of total with a Level of Detail expression

    Perry Swanson

      I'm struggling with a Level of Detail expression. My dataset is from of a survey of people connected to various schools. Each row a contains a question and the answer from one person. The answers are "Agree", "Disagree", "I don't know" and similar. There were different numbers of responses for each school.

       

      I want the visualization to allow comparisons among the schools on each question. That way the user can see, for example, that 40 percent of people responded "Agree" at one school, but only 20 percent responded the same way at another school.

       

      Thus, I think I need to show the percentage of each answer associated with each school rather than the raw number of people who gave each answer. But when I apply a quick table calculation and select percent of total, the denominator is the number of responses in the entire dataset. Instead, I need the denominator to be the number of responses for that question associated with each school.

       

      It seems like a job for a Level of Detail expression. I've drafted various expressions that aren't doing the trick. Here's a screenshot that I hope shows the setup. Can someone offer advice? Thanks.

       

      tableau level of detail for forum post.JPG

        • 1. Re: Percent of total with a Level of Detail expression
          Karen Clark

          Hi Perry,

           

          I am sure there is a way to do this with LOD expressions but I lean towards what I am more comfortable with which would be table calcs in this situation (since for each row you really want Tableau to look at what are the total responses and of the total what percent were each response.  I am attaching a workbook though it is in Tableau 10 built off the superstore data - to compare to what you are looking at I am using State in place of School, Country in place of Question, and segment in place of response.

           

          For your example I would imagine starting with a horizontal bar graph having the rows set up as your image shows then (and this depends on the underlying data and how it's set up) using the number of records on your columns, making that a quick table calc for percent of total (compute using table across).  Pull the response to color and then you can even label with the % of total measure

           

          This is the example I created:

           

          If that doesn't work for what you need do you have a sample workbook with data that you could attach?

           

          Hope this helps!!

          • 2. Re: Percent of total with a Level of Detail expression
            Ivan Young

            Hi Perrry,

            {Fixed School,  Response : SUM(# of recs) / {Fixed School : SUM(# of recs) } will give you the percentage of response for a school.  Not clear if you wanted to analyze the question as well.  But I would go with the above solution using a table calc which seems like it should work.

             

            Regards,
            Ivan

            • 3. Re: Percent of total with a Level of Detail expression
              Perry Swanson

              Thanks to Ivan and Karen for helpful responses. It took me a while to wrap my brain around this. I thought I'd share my final Level of Detail expression in case it's useful to others:

               

              {fixed [Question], [School], [Year], [Response] : sum([Number of Records])} / {fixed [Question], [School], [Year] : sum([Number of Records])}

               

              That expression forces a numerator based on the number of records for each question, school, year and response. The denominator is the number of records for each question, school and year. It's only slightly different from what Ivan suggested.

               

              Thanks again for your help. I will try to pay it forward as I see questions where I can offer some insight.

              2 of 2 people found this helpful
              • 4. Re: Percent of total with a Level of Detail expression
                Ivan Young

                Hi Perry,

                Great that you found your solution.  It sounds like you have also gained a deeper understanding of LODs.  You can mark your own answer correct and close this thread which will help other users with a similar issue find this solution.

                 

                Thanks,
                Ivan