3 Replies Latest reply on Nov 23, 2011 6:23 PM by johnnychung

# Creating calculated measure on specific members of a dimension

Hi all,

i am fairly new to Tableau, and got stucked on this issue.

Assume I have the following table

Date  Account  Amount

1/2/11 Sales    \$1000

1/3/11 Expense    \$200

1/2/11 Sales    \$1000

1/2/11 Expense    \$500

1/3/11 Sales    \$400

1/3/11 Expense    \$50

Once i have loaded the data in Tableau,

I will have “Account” as a dimension with “sales” and “expense” as members .

And in measures I have amount.

But I need to calculate the profit margin

Which is sales amount – expense amount.

and show the results in

profit margin vs Date

How can i achieve this ?

• ###### 1. Re: Creating calculated measure on specific members of a dimension

I did figure out a way, just not sure if this is the best method.

Create a calculated measure for “Total Sales” and use the following formula

CASE UPPER([Account])

when "SALES" then

[Amount]

else

0

End

And create a calculated measure for “Total Expense” and use the following formula

CASE UPPER([Account])

when "EXPENSE" then

[Amount]

else

0

End

Then create a third calculated measure for “profit margin” with

[Total Sales] – [Total Expense]

Then we can use "Profit margin" and "Date" on the worksheet. :)

• ###### 2. Re: Creating calculated measure on specific members of a dimension

Attached would be how I would approach this, very similar to yours.

• ###### 3. Re: Creating calculated measure on specific members of a dimension

Thank you, i do prefer your approach with IIF, makes it much easier to read.

Thank you