CODEWITHSUNDEEP
tableau-api
Filipe Ferminiano
Filipe Ferminiano

Reputation: 8791

Calculate division according to a more granular level in Tableau

I'm trying to create a new metric in Tableau that is division of other 2 metrics:

new metric = metric A / metric B

This works, but when I add this metric as sum(new metric) in my view grouped by a more aggregated field than the original field (e.g. the dataset is in city granularity, but I want to see it by state), the metric is summed and not recalculated. I'm used to Excel pivot table, which recalculates the calculated field according to granularity, how can I get a similar behavior in Tableau?

Upvotes: 0

Views: 605

Answers (2)

Siva
Siva

Reputation: 9101

You need to use the Table calculations for this purpose,

From your explanation looks like City is last level of granularity that is the reason division of 2 metrics is working but when you move to the higher level of granularity you can use LoD or Move to windows calcualtin where you need to give the starting and ending of the partition and then select the partition where you need to calculate.

LoD

https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_overview.html

Table Calculations

https://onlinehelp.tableau.com/current/pro/desktop/en-us/functions_functions_tablecalculation.html

Upvotes: 1

S. User18
S. User18

Reputation: 712

new metric = SUM(metric A) / SUM(metric B)

Sounds like you're trying to make a weighted average, which would be the calculation above.

Think about what is happening in your calculated field. For each city you calculated A/B, then when you use the calculation at the state level, you are summing each of those values.

In the example above, when used at the state level, you are summing A and dividing by sum B.

Upvotes: 1

Related Questions