1 Reply Latest reply on Sep 30, 2016 2:01 PM by Santiago Sanchez

    Percentage Calculation for Distinct Counts

    chakradhar kakani

      Hello Tableau Users,

       

      I'm facing problems with a use case scenario in calculating Percentage for Distinct counts. I have a SHEET1  which shows total distinct counts of category for continent and customer segment breakout. I have another  worksheet SHEET 2 which calculates the percentage by breakout of quarter, continent, customer segment breakout and  which has formula for percentage as  = [distinct container] / [distinct category]. So when it is calculating the percentage it is taking that particular quarter, continent, customer segment breakout distinct counts and giving me the percentages.

       

      Is there any way can we keep the denominator ([distinct category]) for SHEET 2 as the actual total distinct counts as shown in the SHEET 1 as per filter section. and change the numerator same again as per filter selection and calculate the percentages??

       

      Please help. I have attached the sample workbook.

       

      Thank you

        • 1. Re: Percentage Calculation for Distinct Counts
          Santiago Sanchez

          This sounds like a problem that an LOD expression can solve. There are cases when you want to do some aggregations based on the dimensions you have on the view AND you also want to do some other aggregations based on other dimensions; in other words, different levels of aggregations in the same view. It's on such situations that LODs can help.

           

          SUM({ FIXED [Continent], [Department], [Customer Segment] : [distinct category] })

           

          The expression above is asking tableau to calculate distinct categories for each combination of [Continent], [Department] and [Customer Segment], which is what you have on sheet 2. Basically, we are excluding the Date dimension from Sheet 2. With that, you can modify [calcu] to:

           

          calcu.png

           

          [distinct container] is still calculated based on any dimensions you have on sheet 2. [distinct category] now always uses [Continent], [Department], [Customer Segment].