CODEWITHSUNDEEP
calctableau-api
user3581800
user3581800

Reputation: 317

calculated field in tableau with categories

I have dimension with 3 categories and a measure of total sales count I want the ratio between one item of the category and the total

for example - I have a measure of fruit (Apple, Orange, Strawberry) I want the ratio of apple sales divided by total sales

Having trouble with the syntax

sum(([sales(fruit)='Apple']))/sum([sales])

Upvotes: 2

Views: 2041

Answers (2)

Petr Havlik
Petr Havlik

Reputation: 3317

user3581800, very similar solution, but slightly more powerful (and complex as well) is to use so-called Level of Detail (LOD) expressions.

The main benefit is that they will allow you to "overwrite" filters should you need to use them (try adding exclude filter for "Orange, for example). Let's create a newly calculated field Total Sales:

{ FIXED : SUM([Sales]) }

This will produce a total sum of sales no matter what, and then you could create a calculated field as Nicarus suggested in his answer, even though this one should be more flexible:

SUM( IIF([Fruit] = "Apple", [Sales], 0) )  /  SUM( [Total Sales] )

Read more about the power of LOD Expressions on Tableau blog.

Upvotes: 0

Nick
Nick

Reputation: 7441

You can use a conditional aggregation to do that. Basically you just embed an if statement inside of the aggregate function like so:

SUM(IF [fruit] = 'Apple' THEN [sales] END) / SUM([sales])

The numerator SUM ignores all non-Apples, while the denominator includes all fruits.

Upvotes: 1

Related Questions