3 Replies Latest reply on Aug 4, 2016 11:50 AM by David Pacheco

    Member Calcualtion within One dimmesion

    David Pacheco

      Hi All,

       

      I have been trying to create a calculated field using the members inside one dimension but cannot seem to get it right. My data has only one column for amount with two dimensions. I have also added a third dimension calculated field (Profit Margin) to aggregate the other two dimensions, but when I add Totals to the view, my calculated dimension field does a sum all. What I would like to have is the calculated Profit Margin Field dimension show the net of the two members from the Acct Category (Revenue - Cogs) with the correct Total as showing below.

       

       

      Dimension        

      Profit Margin =  Acct Category = Revenue - Cogs

       

      Profit
        Margin
      Acct CategoryCategory
      Profit MarginTotal   (962,180.00)Shows the right Total
      RevenueTotal     667,587.00
      Furniture     464,088.00
      Office Supplies     190,212.00
      Technology       13,287.00
      CogsTotal  1,629,767.00
      Furniture     277,918.00
      Office Supplies     528,915.00
      Technology     822,934.00
        • 1. Re: Member Calcualtion within One dimmesion
          Stoyko Kostov

          Hi David,

           

          You need to keep Acct Category and Category in different axes if you want to use the 2 values in Acct Category for the purpose of computing profit margin.

           

          Here's what I did:

           

          1. Remove "Profit Margin" from Rows

          2. Move "Acct Category" from Rows to Columns

          3. Click Analytics, drag Totals to your table, choose Column Grand Totals

           

          Now you have your totals per Acct Category, and the last thing that remains to be done is compute profit on a row-by-row basis. This is done using Table calculation.

           

          4. Duplicate Amt

          5. Rename the copy obtained above to ProfitMargin (observe no space; if you want to use space, delete the "Profit Margin" you defined first)

          6. Drag Profit Margin to Text

          7. Click on SUM(Profit Margin), choose Quick Table Calculation - Difference

           

          You get the margins you need - now you can format them a bit to beautify the presentation.

           

          8. Click Text, click the "...", replace what you see with <SUM(Amt)> <Difference in SUM(ProfitMargin)> (note the tab between the <SUM(Amt)> and <SUM(ProfitMargin)>; Tableau doesn't allow you to insert tab directly, but you can copy-paste it from another editor). Now make sure your text is left-cenetered.

           

          9. Right-click on Cogs column in the table, choose Edit Axis, replace it with "Cogs  Profit Margin"

           

          You get the idea - you can continue formatting it as you like.

           

          I'm attaching my result - Let me know if you have any questions.

          • 2. Re: Member Calcualtion within One dimmesion
            David Pacheco

            Thanks Stoyko,

             

            Your option seems like a good work around with the only exception that it modified the format I had originally wanted. Doing some further research, I came across a good article that describes how to modify the Grand Totals only, yet keeping the two categories in the Rows shelf.

             

            The article below shows how to Customize Grand Totals. I will give it a try and upload a workbook with the desired results.

             

             

            http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/

            Thanks.

            • 3. Re: Member Calcualtion within One dimmesion
              David Pacheco

              Hi Stoyko,

               

              I tried the solution from the Customized Grand Totals article and it worked. I'm uploading the workbook to see if you can identify potential issues or improve the calculated field (Gross Margin) I created, again using the concepts from the article above. The calculated field formula I used display as follows:

               

               

              if MIN([Acct Category])!=MAX([Acct Category]) THEN
              SUM(IF([Acct Category]="REVENUE") THEN [Amt] END) - SUM(IF([Acct Category]="Cogs") then [Amt] END)

              ELSE
              SUM( [Amt] )
              END

               

              Then I formatted the Grand Total label and replaced it with Gross Margin.

               

              Thanks.