1 2 Previous Next 17 Replies Latest reply on Aug 7, 2012 11:08 AM by Tracy Rodgers

# How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

I am trying to build a graphic that shows total revenue sales, by district and then further by sales rep.  I have the table showing this information visualized in a horizontal bar graph, in \$XXXK figures, with a Grand Total bar at the bottom to give perspective.  Now I want to annotate each bar, with the % of sales each SR reps' figures represents to the total.  How do I accomplish this?

Thanks,

Tony

• ###### 1. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

For Example:

Total Revenue By District By Sales Rep

Central

Bob:       |||||||||||\$5,000K<---- 33.33% Of Total Revenue (How do I get this annotation, [besides manually])

Grand Total: ||||||||||||||||||||||||||||||||||||||||||||||||||||||| \$15,000k

• ###### 2. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Hi Anthony,

By creating a calculated field similar to the following and placing it on the label shelf will get you this label:

'\$' + str((if sum(Sales)<10000 then left(str(sum(Sales)),1) + ',' + mid(str(sum(Sales)),2, 3)

elseif sum(Sales)<100000 then left(str(sum(Sales)),2) + ',' + mid(str(sum(Sales)),3, 3)

elseif sum(Sales)<1000000 then left(str(sum(Sales)),3) + ',' +mid(str(sum(Sales)),4, 3)

else str(sum(Sales))

end)) + ' = ' +str(round((SUM([Sales]) / TOTAL(SUM([Sales]))*100),2)) + '% of Total'

Hope this helps!

-Tracy

• ###### 3. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

I'm sorry, I really appreciate the reply but this is my third day with Tableau and I do not follow..

• ###### 4. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Hi Anthony,

My apologies. By creating a calculated field and placing it on the label shelf of the marks card will label each bar with whatever is in the formula.

The above formula makes it so that the output would be similar to:  \$5,000 = 33.33% of Total

In order for Tableau to use both the percentage and currency in the label, the measure needs to be converted to a string, hence the STR functions that are applied in several portions of the formula. Since the measure has been converted to a string, it loses all of it's formatting--therefore, by creating a formula within the formula (if sum(Sales)<1000...end) allows for the commas. Anything that is between quotation marks is also considered to be a string and thus will be added to the label.

Finally, the following portion of the formula actually finds the percent of total: SUM([Sales]) / TOTAL(SUM([Sales]))

It is multiplied by 100 and rounded off so that the output will look like a percentage.

I hope this explains everything in the formula and that I haven't confused you more.

The following link provides information on all the different functions:

http://onlinehelp.tableausoftware.com/current/pro/online/en-us/functions.html

**If you only want the percent of total on the view, place Sales on the label shelf, right click on it and select Quick Table Calculation-->Percent of Total.

Hope this helps!

-Tracy

• ###### 5. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Label shelf?

• ###### 6. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Here's a picture of the label shelf. It's sitting on what we refer to as the Marks card.

• ###### 7. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Alright I got it all figured out, but now it is going to far too many decimal places.  If I am not mistaken the last line of the equation:

end)) + ' = ' +str(round((SUM([Sales]) / TOTAL(SUM([Sales]))*100),2)) + '% of Total'

With the "),2))" specifically is supposed to control that but when included in the formula it comes back as an error.  How do we negotiate this?

• ###### 8. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Hmm...so you want only 1 decimal place or none at all for the percentage?

Your right that the portion referenced will determine the number of decimal places. If you only want decimal place, change it to a 1, if you want no decimal places, then replace the 2 with a 0 or remove a value altogether.

What is the error that you are receiving? Maybe you can copy and paste the calculation that you are using?

-Tracy

• ###### 9. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Hi Tracy,

First off thank you very much for your continued support and help, couldn't have gotten this far without you and really appreciate it.

I re-entered the calculation today and I am getting no decimal points in the percentage, which is fantastic.

But I am getting: "\$123456.1234567890 = 50% of Total"

Is there anyway I can just deal in whole numbers and units of thousands in the revenue line?\

Thanks,

Tony

• ###### 10. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Ultimately what I am looking for is:

"\$1,234K = 10% of Total"

• ###### 11. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Hi Anthony,

Glad we're getting close and hopefully learning along the way! This is where the "if sum(Sales)<10000..." portion of the calculation comes into play. So a clause similar to the following should be added to your formula:

"\$" + (if sum(Sales)<10000 then left(str(sum(Sales)),1) + ',' + mid(str(sum(Sales)),2, 3) + 'K'

elseif sum(Sales)<100000 then left(str(sum(Sales)),2) + ',' + mid(str(sum(Sales)),3, 3) + 'K'

elseif sum(Sales)<1000000 then left(str(sum(Sales)),3) + ',' + mid(str(sum(Sales)),4, 3) + 'K' end) + "=" + the percentage part of the formula

Let me know if you have further questions!

-Tracy

• ###### 12. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Hi Tracy,

So hopefully this is the last question, I've now added another dimension to my charts and need help figuring it out.  I have started comparing actual sales to sales targets and would like to show actual sales as a percentage of the sales target.  I have labeled the sales target as Total (Target) and have tried adapting the above-equation, by using SUM([Sheet1 (Total Deal Target.xlsx)].[Total (Target)]) but have been frustratingly unsuccessful.

I was wondering if you could help me resolve this once and for all?

Thanks,

Tony

• ###### 13. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Also, will there be a way to have two different labels for the two different Totals (Total and Total (Target)) on the sheet, right now when I add the label it attaches itself to both, giving identical results based on the Total.

Example:

Total: \$1,000

Total (Target): \$2,200

Total: \$1,000 = \$48% of Total

Total (Target): \$1,000 = \$48% of Total

• ###### 14. Re: How can I Annotate a point/mark/area to show the value as a percentage of total revenue?

Hi Tony,

Would it be possible to post this workbook (saved as a twbx file)? Since a table calculation is being used (the Total function), the way the calculation is being computed may need to be edited.

-Tracy

1 2 Previous Next