4 Replies Latest reply on Feb 14, 2020 9:13 PM by Sam Badie

    How to get the difference between two total percentage (pane)

    Sam Badie

      Hello,

       

      I have two table calculation across pane (percentage of total) one for L and the other for S for each cg1 and across countries. I would like to add a third row which contains subtraction of (the table calculation of percent of total for L) & (the table calculation of percent of total for S)

      As shown in the screenshot, any help please?

       

      Dimensions: Sales channel, Country, CG

      Measures: Sales_Value

       

      Capture.PNG

        • 1. Re: How to get the difference between two total percentage (pane)
          Rohit Yeruva

          Hey Sameh,

           

          Can you attach a workbook so that we can show how to do this.

           

          Regards,

          Rohit

          • 2. Re: How to get the difference between two total percentage (pane)
            Joe Oppelt

            You use:

             

            LOOKUP( (whatever makes those values), -1)

             

            That will look back one "hop" in the table.

             

            But you're going to have to set the proper settings on that table calc, and I fully agree with Rohit.  It's far easier to show you in an actual workbook than to describe it.

            • 3. Re: How to get the difference between two total percentage (pane)
              Joe Oppelt

              It's not going to be as straightforward as you might have hoped.  For one thing, the chunks of rows for S and L are driven by the dimension values.  There is not a 3rd value into which you can shove your DIFF values.

               

              But look at the attached.  I hacked up a DIFF sheet and placed it on a dashboard below your data sheet so that it looks like a DIFF chunk that is part of the overall viz.

               

              How did I get that DIFF sheet?

               

              Look at Sheet 1(2).  It's a copy of your original, but I made my own %of total calc.  (Open the calc editor and just drag your quick table calc pill into it, and the syntax automatically gets generated for you.)  We need our own calc so that we can use it in the next calc.

               

              Sheet (3)  Here I dragged Channel into the Details shelf.  Edit the Percent of Total table calc settings and see what I did there.  Since Channel is no longer on rows or columns, I can't reply on PANE(down) or TABLE(across), etc., and I have to explicitly tell tableau how to address the dimensions.  And you can see that I have both values now appearing in each CAT value.  (If there are two values to display.  Some CATs don't have values for one or the other Channel.)

               

              Also here I made a DIFF calc to subtract the two values.  You can see that 20.17-6.86=13.31, for example.  Edit that table calc setting:

               

               

              You will notice a pulldown for nested table calcs.  Here the Percent table calc is nested in the DIFF calc.  Pull down and see that the setting for Percent calc matches what is in the pill on the text shelf, and that it is different from the setting for the actual DIFF calc!  Yes, you can have one calc running one way, and another calc running another way, and here we need precisely that.

               

              Go to sheet (4).  Here I took the percent calc off the text shelf and I reshaped the sheet a bit to approximate the shape of the original.  I created a bogus pill to put the value "Diff" on the sheet, and I hid all the headers.  This is how we shove a fake dimensional-looking row at the bottom of a data sheet.

               

              And then I put all this on the dashboard.


              See attached.

              • 4. Re: How to get the difference between two total percentage (pane)
                Sam Badie

                Thanks so much, that was so helpful.