3 Replies Latest reply on Feb 29, 2016 11:51 PM by Randy Adhitama Setiadi

    How to use 2 table calculations in 1 measure

    Randy Adhitama Setiadi

      Hi all,

       

      I was wondering how can I use table calculations for percent of total first and then find the difference between the dimension.

       

      Here's the example:

       

      First, I make the measure show percent of total from "Range Value"

       

      Then i need to show the difference between Brand A and Brand B in each "Range Value", I notice that we cannot use 2 table calculations functions in 1 measure (can we?), so is there any good solutions for this?

       

      Thanks

       

      Randy

        • 1. Re: How to use 2 table calculations in 1 measure
          Adam Crahen

          Hey Randy-

           

          If you create the % of total as an LOD you can then aggregate it again for the difference

           

          Percent of Total

          {fixed [Range Value], [Brand] : sum([Value])}/{fixed [Brand] : sum([Value])}

           

          Then I did a table calc on the SUM([Percent of Total]) Difference along Brand

           

          2016-02-22_21-09-01.png

          9.0 workbook attached.

          2 of 2 people found this helpful
          • 2. Re: How to use 2 table calculations in 1 measure
            Bora Beran

            You can use two table calculations in one field. You can use a lot more than that actually. These are nested table calculations. You first build separate calculations and each of these could have separate formula even separate addressing/partitioning/sort etc. settings and then you refer to them from another calculation.

             

            The easiest way to do this first drag SUM(Sales) table calc to the data pane. This will save it as a calculated field. Let's say you call it Calculation1. Then you can write difference from calculation using a reference to Calculation1. As you see in the screenshot below, you will also get a drop down in table calculations dialog for each level of nested table calculation you have. Since Calculation2 (the difference from calculation shown at the bottom of this dialog) refers to Calculation1, it is shown in the drop down. Tableau will first calculate Calculation1 with the settings you use in this dialog and then take its results and use as input to Calculation2.

             

            Screen Shot 2016-02-22 at 11.18.35 PM.png

            1 of 1 people found this helpful
            • 3. Re: How to use 2 table calculations in 1 measure
              Randy Adhitama Setiadi

              Hi Adam,

               

              Sorry to reply too long, because I still think why the calculations with table calculation and the one that your way it's different in my original workbook. But now I know the problem.

               

              This still the same between the one with table calculation with percent of Total with our created calculated field

               

              But now it's become different. The reason is I put "Last Date Show" field. I need this variable to calculated only the last data from the same ID that "have a value" not only null value. I attached the new workbook with new data source. How can we deal with these?

               

              Hi Bora,

               

              Thanks to your reply, I still figure out your way. Hope you can also help me with these new problem :d

               

              Thanks

               

              Randy

              1 of 1 people found this helpful