5 Replies Latest reply on Feb 16, 2016 6:21 AM by Dan Sanchez

    How to show a new row as a percentage of prior two rows

    Collin Bath

      Hi,

      I have a text table with two rows in it. The first column is a dimension which I have called measure, (excuse the name if confusing in the context of Tableau). Following in the table are 8 different measures. What I want to do is show a third row which is the percentage of the two above rows. In other words new row = (std margin row / sales row) * 100

      In the example above the new rows would equal 33.18 for column Q216 PRELIM and 33.06 for column Bill QTD. I appreciate that usually you would have separate actual measures for sales and std margin but in this case the format I have is driven from the data source. Visually it seems such a simple thing to do but I am fairly new to Tableau and cannot work out how to do this.

      Any help would be much appreciated! I have included the packaged workbook.

      Regards

      Collin

        • 1. Re: How to show a new row as a percentage of prior two rows
          Dan Sanchez

          Hi Collin!

           

          We've got a couple options available to us to get the percentage values.  I've attached a revised copy of your workbook with two solutions.  The first is to create a separate worksheet for the percentage value and then combine both worksheets on a dashboard.  For the table calculation we will need to set the Compute Using to [Measure].

           

          The second option is to duplicate the [Measure] dimension and use a trick with the SIZE() function to force the percentage calculation into the Grand Total row.  This requires you to set the Compute Using setting for the calculations to use [Measure (copy)].  Both solutions require us to use the LOOKUP() function to calculate the values.

           

          Another option would be to pivot the columns when you connect to the data.  This might make things easier down the road but it's not a requirement if you would prefer to keep the data in its current structure.

           

          Hopefully this help get you started.  Thanks Collin!

          • 2. Re: How to show a new row as a percentage of prior two rows
            Collin Bath

            Dan,

             

            Thank you very much for this. I have managed to implement the first suggestion that uses the dashboard and calculation and I think I understand. I have a few questions on the second approach and will follow up with these tomorrow.

             

            Regards

             

            Collin

            • 3. Re: How to show a new row as a percentage of prior two rows
              Collin Bath

              Hi Dan,

               

              Sorry for the delayed response! As mentioned I implemented your approach one above which is working for me but I am now trying to understand it.  First let me try to understand your option one before moving on to the more complex option two.

              I may be confused by the way the partition is working which I believe is dictating how this calculation processes the data. Is it a separate partition per measure? E.g. The formula (SUM([Q216 PRELIM]) /LOOKUP(SUM([Q216 PRELIM]), -1)) * 100

               

              is calculating based on the two separate partitions shown in red below?

              In which case I believe that the first part of the formula which is the SUM([Q216 PRELIM)) is bringing back 43.1 and 14.3 separately.  Is this correct?

               

              If so the second part of the calc. using LOOKUP appears to be looking at the previous row and dividing the two. So new calc record = record 2 / record 1. Is this correct?

               

              If I have the above correct I am confused as to what is happening for the first record. Surely this would mean the first part of the calculation finds 43.1 and then the second part looks for a prior row or row 0 which does not exist?

               

              What happens here, does it just ignore the calc. as no row 0 exists?

               

              If I have the above totally wrong is there any chance you could provide some guidance on how the calc. is processing the different rows?

               

              Once I have the above clear in my mind I will move on to the second option.

               

              Regards

               

              Collin

              • 5. Re: How to show a new row as a percentage of prior two rows
                Dan Sanchez

                Hi Collin!

                 

                You've pretty much got it in terms of what's going on.  I'll try to explain in another way though just to make sure.

                 

                So the way LOOKUP() functions in relation to the (SUM([Q216 PRELIM]) /LOOKUP(SUM([Q216 PRELIM]), -1)) * 100 formula is that it it will evaluate for each row, once for the Sales value (43.1m) and again for Std Margin (14.3m).  When the calc is evaluating for Sales it will effectively be doing this:

                 

                (43.1m / (value that does not exist) ) * 100

                 

                Since we are trying to divide by a value that isn't there (the cell previous to Sales does not exist) Tableau will return a result of null.

                 

                When we move on to evaluating the result for Std Margin, the formula will be doing the following:

                 

                (14.3m / 43.1m) * 100

                 

                So this calc actually does return two different values per column, it's just that one of the values is null so it doesn't appear on the worksheet.  If you take a look at the "2 sheets + dashboard" worksheet and look in the bottom left corner, we can see that there are indeed 4 marks being rendered but two of them are hiding out in the realm of null

                 

                Hopefully that makes things a bit clearer on how LOOKUP() is doing the math.

                 

                Thanks Collin!

                1 of 1 people found this helpful