3 Replies Latest reply on Nov 9, 2017 8:48 PM by Abhilash Sharma

# Comparing measures to different levels of a hierarchy

I am using the following datasource in tableau:

ContinentCountryCitySales
EuropeUnited KingdomLondon10
EuropeUnited KingdomSheffield20
North AmericaUSANew York40
EuropeGermanyBerlin30

In Tableau the location is organised into a hierarchy (Continent -> Country -> City) and the Sales is a simple measure.

I need to show the share of sales in a specific city vs the total sales in the parent country/continent. For example if the users select London from a filter or parameter and I need to show them:

• Share of Country Sales: 33% (10 in London / 30 in UK)
• Share of Continent Sales: 17% (10 in London / 60 in Europe)

There must be an easy way of doing it.

• ###### 1. Re: Comparing measures to different levels of a hierarchy

Hi Peter,

Yes, there is an easy way to do this via Level of Detail expressions.

Step 1: Create Share of Country Sales Calculated Field as below:

{ FIXED [City] : SUM([Sales])} / { FIXED [Country] : SUM([Sales])}

Step 2: Create Share of Continent Sales Calculated Field as below:

{ FIXED [City] : SUM([Sales])} / { FIXED [Continent] : SUM([Sales])}

Step 3:

Change the display format of both of these fields to Percentage.

Output:

Hope this helps.

-Abhilash

3 of 3 people found this helpful
• ###### 2. Re: Comparing measures to different levels of a hierarchy

wow it cannot be more precise answer. you really took the effort to explain it and really highlighted my gap of my knowledge in LOD expressions. many thanks

• ###### 3. Re: Comparing measures to different levels of a hierarchy

No problem Peter.

I'm glad I was able to help.

-Abhilash