5 Replies Latest reply on Jul 26, 2018 1:35 PM by Joe Oppelt

    Multiplying Across a Row

    Fabiola Agramonte Bravo

      Hello,

       

      I am trying to figure out how to multiply all of the values in one row.

       

      I have this table and I would like another column to be added at the end that is the product of all the numbers in each row.

      How can I do that?

       

      Thank you,

      Fabiola

        • 1. Re: Multiplying Across a Row
          Joe Oppelt

          You need to break apart those metrics into their own measures, and then handle them as you need.


          In the attached, I did it for two of them.  See Sheet 2.

          • 2. Re: Multiplying Across a Row
            Fabiola Agramonte Bravo

            Hi Joe,

             

            Thanks, that really helped. I've sorted out by quarter and now the calculated field instead of it just being numerators, it's numerator/denominator. This is how I get the %.

             

            My trouble now is if you see the NA sheet you'll see a row that says total and that is the sum of numerators divided by sums of denominators. How do I get this done in the sheet you created? If I try to add Grand Total it will only sum the percentages an I need it to be (sum of numerators) / (sum of denominators).

             

            Thanks you for your help,

            Fabiola

            • 3. Re: Multiplying Across a Row
              Joe Oppelt

              See attached.

               

              You need to do the same thing with denominators that I did with numerators.


              And then you need to do SUM(whatever numerator)/SUM(whatever denominator)

               

              For the record, you could nest LODs into one calc if you don't want to have intermediate calcs.

               

              { FIXED ... } / { FIXED ... }

               

              I actually like to have the intermediate calcs just for ease of debugging and for someone else to pick up my workbook a year from now and make changes.  But your sample data has only 4 metrics.  If your actual data has 40 metrics, you'll still need to have a final calc for each one, but what I did here would require 80 more intermediate calcs, and that can get very cumbersome.

              • 4. Re: Multiplying Across a Row
                Fabiola Agramonte Bravo

                I understand what you did but I need the first calculation you initially sent me to still be in the column and the total (the sum of numerators / sum of denominators) to be a row below.

                 

                So it looks something like this in a way :

                Sorry for my awful drawing, I just wanted to show you what it should look like ( with numbers of course).

                 

                I tried to duplicate the Measure Names so I could do the same thing for rows as you did for columns, but I can't do that. I can only place measure names in both rows and columns if I have the same filters for both and that's not what I need.

                 

                Do you think there's a way to do this?

                 

                Thank you,

                Fabiola

                • 5. Re: Multiplying Across a Row
                  Joe Oppelt

                  That's more of an excel-like treatment.  Tableau will treat all measures (which all of these calcs are) the same.


                  So what you'll need to do is create two separate sheets -- one for the upper portion of the viz, and one for the totals.  Format them the same and position them one above the other on a dashboard so that it looks like one sheet to the user.