7 Replies Latest reply on May 3, 2013 9:54 AM by amanda.stanke

# Help with Manual Percentage of Total Calculation

Hi there,

What I'm ultimately trying to do is show multiple labels on a pie chart that I've created - This reference material is fantastic but I'm not sure how to write out one of the manual calculations:

I work at a company that has multiple Business Units. Every quarter my department invests X amount of dollars into each Business Unit. Ultimately what I'm trying to display in my Pie Chart is:

1. For Q2, how much money did we invest in each Business Unit (easy to do, I pulled in the SUM of our total quarterly investment, did a Quick Calculation so that the investment per BU is a little piece of the pie): So I have the Investment by BU, which is great, and the Pie shows the % of that investment compared to total investment... But now I want to show:

2. Next to the absolute \$ value of the investment what the % value is. I am sure I can do it following the Tableau directions, but I'm not sure what the manual calculation would be that I would type in to update the label. For example, I can see that BU 2 was allocated about 50% of the total investment - I would like to show the \$3.8 mil dollar amount next to the % allocation.

• ###### 1. Help with Manual Percentage of Total Calculation

You can create a calculated field and use something like this:

STR(SUM([Sales])) + "

" + STR(SUM([Sales]) / TOTAL(SUM([Sales])))

Note the way to get the line break. The results will be something like:

\$225,555

50.3%

You could also upgrade to V8, which makes this a lot easier. But my best suggestion is to dump the pie chart, and go with a bar chart, it much easier to compare bar lengths than pie slices.

--Shawn

• ###### 2. Re: Help with Manual Percentage of Total Calculation

THANK YOU!!

And I couldn't agree more in terms of choosing a different chart type - Unfortunately my stakeholders like their pie charts.

Another (hopefully) quick question - This is ultimately the calculation I used to essentially express:

\$Quarterly dollars allocated

% of total

"\$"+STR(ROUND(SUM([Total MDF (USD)])))+ "

" +""+ ""+ (STR([% MDF])+"%")

Now, I was able to use ROUND to round my \$Quarterly dollars to whole numbers - Is there a way to also add commas to the expression?

Here's my updated pie chart: I'd like to, for example, express \$3967314 as \$3,967,314

THANK YOU again!

• ###### 3. Re: Help with Manual Percentage of Total Calculation

What's your data source? Are you using an Extract?

--Shawn

• ###### 4. Re: Help with Manual Percentage of Total Calculation

Amanda,

Try to reshape Total MDF (USD) using right - click, then Default properties -> Number Format Regards,

Cristian.

• ###### 5. Re: Help with Manual Percentage of Total Calculation

It's a string calculated field in V7, your suggestion won't work. Even if you format the number fields feeding the calculation, the calculation itself destroys the formatting. http://community.tableau.com/message/181215#181215

--Shawn

• ###### 6. Re: Help with Manual Percentage of Total Calculation

A useful one is using the LOOKUP([number],0) function to add the thousands seperator to numbers.

Then you might not need the ROUND(). And you could also use INT() which sometime works better than ROUND()

--Shawn

• ###### 7. Re: Help with Manual Percentage of Total Calculation

SHAWN - You're my hero!

(ok, sorry for the ALLCAPS; my Forum etiquette knows better!)

This did it:

"\$"+STR(LOOKUP(ROUND(SUM([Total MDF (USD)]),0),0))+ "

" +""+ ""+ (STR([% MDF])+"%")

And thanks to everyone who answered - I'm new here and this is such a fantastic community!