Calculation contains errors for KPI in Tableau

Question

I'm trying to create an API which will tell me the sum of metals awarded across the top 10 countries in my data of the olympics so I can create a visualization of it.

When creating the API i'm specifying SUM[medals] IN Countries which is a syntax error related to the IN operator, also not sure how I would have it cycle through only the top 10 countries on the list rather than them all. Any ideas?

enter image description here

Answer 1

Assuming you have a set called Top_Countries defined however you like,

Then you can write a calculated field called, say, Top_Country_Medals defined as if [Top_Countries] then [medals] end

Alternatively, say you don't want to make a set, but you instead already have a string field called Top_Country_Name that has a value if the current country is in the top 10 and is null otherwise.

In that case, the calculated field would just be if not isnull([Top_Country_Name]) then [medals] end

Then your calculated field will evaluate to the # of medals for Countries that belong to the Top_Countries set (or alternatively, the countries that have a non-null value in the [Top_Country_Name] column), and to null for other Countries. Then you can place your new field Top_Country_Medals on any shelf as a measure and use whatever aggregation function you like - SUM, MIN, MAX, AVG etc

Note an if expression without an else clause evaluates to null if the if condition is not satisfied. And all aggregation functions silently ignore null values, leading to very simple calculation expressions if you take advantage of those facts.

Just naming a set in a calculated field, tests membership. You don’t need the IN keyword for that.