3 Replies Latest reply on Feb 23, 2017 7:05 AM by Jamieson Christian

    Calculation with Measures filtered by a Dimension

    Ibrahim Wahba

      Hi,

       

      I am working with some data that is evaluating the enrollment in a program over a few years. I have a measure that represents each person. One dimension filters those who applied as Y/N. Another dimension filters those who enrolled as Y/N. Is there a calculation I can create to use the filtered measure to get a percent yield?

       

      This is the calc I'm trying to create:

       

      Enrolled / Applied = Percent yield

       

      I don't have the ability to change how the data is brought to Tableau.

       

      I am hoping to display the percent yield trend with a year dimension.

       

      Any thoughts are appreciated!

       

      Thanks!

        • 1. Re: Calculation with Measures filtered by a Dimension
          Jamieson Christian

          Ibrahim,

           

          You should be able to use a simple aggregation calculation in conjunction with some IF checks on your boolean dimensions. For example:

           

          [Percent Yield]

          SUM( IF [Enrolled] THEN 1 END ) / SUM( IF [Applied] THEN 1 END )
          

           

           

          The nice thing about simple aggregation calculations is that they automatically adapt to the level-of-detail in your view, so they can be used in conjunction with e.g. hierarchical drill-downs.

           

          Does this yield what you want? If not, please attach a packaged workbook and we can take a closer look.

          1 of 1 people found this helpful
          • 2. Re: Calculation with Measures filtered by a Dimension
            Ibrahim Wahba

            Thank you for this.

             

            It gave me some ideas and I was able to make it work with this calc:

             

            SUM( IF [Enrolled] = "Y" THEN [Number of Records] END ) / SUM( IF [Applied] = "Y" THEN [Number of Records] END )     */ Number of records is a measure

             

            The issue I am having now is that the numbers don't line up perfectly. They are close but not exactly correct. The filters on the measure return these numbers:

             

            Enrolled = 4,065

            Applied = 5,346

             

            The correct yield is .7586 (calculator) but Tableau is displaying .7483

             

            It appears that the yield in Tableau is sometimes higher and sometimes lower than the correct yield. There isn't a consistency. My organization prevents me from uploading any type of workbook so unfortunately I am left to just describe the situation.

             

            Thanks!

            • 3. Re: Calculation with Measures filtered by a Dimension
              Jamieson Christian

              Ibrahim,

               

              When an aggregate division doesn't yield the number you're expecting, it usually means one of two things:

               

              • The numbers you used to validate the result were already subject to some level of aggregation. Grand total aggregations are evaluated separately from the individual aggregations on the view, so that e.g. the grand total of an average will not be simply the sum (or average) of individual row averages.
              • The aggregation involves a calculation that often doesn't roll up as expected. COUNTD() is a great example of a calculation that tends to produce confusion at the grand total level. These situations often indicate the need for table calculations to produce the correct grand total computation (for example, to achieve a "SUM of COUNTD").

               

              Your description of the situation does not immediately suggest that either of the above apply. Unfortunately, without being able to see your workbook, I cannot diagnose further. Consider anonymizing your data so that you can upload a workbook without revealing sensitive information. For more information about anonymizing a Tableau packaged workbook, please see this video: Anonymize your Tableau Package Data for Sharing

               

              Good luck!