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

# Member Calcualtion within One dimmesion

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 Category Category Profit Margin Total (962,180.00) Shows the right Total Revenue Total 667,587.00 Furniture 464,088.00 Office Supplies 190,212.00 Technology 13,287.00 Cogs Total 1,629,767.00 Furniture 277,918.00 Office Supplies 528,915.00 Technology 822,934.00
• ###### 1. Re: Member Calcualtion within One dimmesion

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

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.

Thanks.

• ###### 3. Re: Member Calcualtion within One dimmesion

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.