4 Replies Latest reply on Jul 1, 2015 9:30 AM by Luis de la Torre

# Custom Geographic Regions and Calculating Percentages By Region

There many questions defining custom regions and doing calculations over them.

This question: Data at Area level (different than the provinces level) has a great answer in the form of a workbook that calculates the sum of sales by geographic region.

My question is: rather than calculating and displaying the sum of sales by custom geographic region, how can I calculate and display the percentage of sales by custom geographic region?

As a shot in the dark, I tried changing SUM([Sales]) in LOD Region or State to SUM([Sales]) / TOTAL(SUM([Sales])), but I get the error "Level of detail expressions cannot contain table calculations or the ATTR function."

I am using Tableau 9.0.2.

• ###### 1. Re: Custom Geographic Regions and Calculating Percentages By Region

Hey Luis,

What is your LOD Sales computation like?

If you do a SUM([Sales]) / SUM {Fixed Region: Sum([Sales])}), that should give you the right figure.

Cheers,

Kenny

• ###### 2. Re: Custom Geographic Regions and Calculating Percentages By Region

I don't know enough to answer your question, or what exactly your answer means, but I'll try to give more useful info.

In the attached workbook (from the answer Re: Data at Area level (different than the provinces level) ) the measure "LOD Region or State" is:

IF [State or Region]='Region' THEN

{FIXED [Region] : SUM([Sales]) }  ELSE

{FIXED [State] : SUM([Sales]) }  END

When you say to "do a SUM([Sales]) / SUM {Fixed Region: Sum([Sales])})", does that mean to change the LOD to that? There is also in the Marks section the calculation SUM([LOD Region or State]). I'm not sure if I would need to change that.

• ###### 3. Re: Custom Geographic Regions and Calculating Percentages By Region

Hi Luis,

It depends on what you want your numerator and denominator to be.

For instance in US Superstore, your denominator could always be the total of ALL sales. Your calculations would then be for instance:

- Region % = sum( {fixed Region:Sum(Sales)} )  / sum( {Sum(Sales)} )

This gives the sum of sales at each region divided by the total sales. The curly brackets around sum(sales) would total all sales regardless of detail.

- State % = sum( {fixed State: Sum(Sales)} )/ sum( {Sum(Sales)} )

This gives the sum of sales of each state divided by the total sales.

Does this clarify?

Kenny

1 of 1 people found this helpful
• ###### 4. Re: Custom Geographic Regions and Calculating Percentages By Region

Yes. Great. Thank you!