5 Replies Latest reply on Oct 18, 2018 6:56 AM by Mahfooj Khan

Dividing in a calculated field

I have a Transaction table listing orders and returns. The column describing the nature of each transaction (either order or return) is called [Type]. The column [Quantity] lists the amount of units for each transaction. I want to create a calculated field (called Return%), calculating the returns as a percentage of orders. I used the following formula - SUM(CASE [Type] WHEN 'Refund' THEN [Quantity] ELSE 0 END) / SUM(CASE [Type] WHEN 'Order' THEN [Quantity] ELSE 0 END).

It's returning zero percent. I'm displaying this metric as a Label on a graph, with Date on the horizontal axis (aggregated by month) and Quantity on the vertical axis.

Column Shelf - Month(Date)

Row Shelf - SUM(Quantity)

Color Mark- [Type]

Label Mark - AGG(Return%)

Attached is a similar graph from mock data. Although the calculated field in this case is a meaningless number, the idea is the same.

Tableau Desktop 2018.2.2

Any guidance would be appreciated.

• 1. Re: Dividing in a calculated field

Hi,

Try this,

{FIXED DATETRUNC('month', [Order Date]): SUM(CASE [Segment] WHEN 'Corporate' THEN [Quantity] ELSE 0 END)}

/

{FIXED DATETRUNC('month', [Order Date]): SUM(CASE [Segment] WHEN 'Consumer' THEN [Quantity] ELSE 0 END)}

Let us know if this help.

Mahfooj

1 of 1 people found this helpful
• 2. Re: Dividing in a calculated field

That works (thank you!), however i have a filter [Product_Id] and when i filter a specific product, the calculated field still returns values as if it's not filtered. If there a way for the calculated field to take into account the filter?

I have another question - The attachment i sent you has the two values stacked. I want the bars to be unstacked, but not side-by-side, rather one in front and the other behind it. Can you help me on this? (Do i have to open a new discussion for this?)

• 3. Re: Dividing in a calculated field

For first one: Put your filter on context like this

Not required

you can create separate calculated fields for Consumer and Corporate segments quantity and using dual axis you can achieve this, See below screenshot.

hope this help.

Mahfooj

1 of 1 people found this helpful
• 4. Re: Dividing in a calculated field

SUPERB ! 2 for 2

Thank you