Product Tour
forums
Use these forums to ask questions and discuss Tableau.
Percent Calculations
Posted December 18th, 2007 by Igor Kaludjer
in Calculations
Let's say I have a graph showing Sum(Sales) by Products (Color shelf) and Years (Column shelf). Now I want to see percentage of Sales for each product in any Year so I do a Analysis->Percentage of->Column in Pane and that works fine.
Now comes the tricky part. I want to switch between absolute values and percentages back and forth so I put Measure Names on Filter shelf and check the "Show Quick Filter" option. Now I have two measures on the quick filter shelf: 1. SUM(Sales) and 2. % of Total Sum(Sales), but when I click the SUM(Sales) the percentage disappears. Any ideas how to get around this?
Comments
One thing you could do to see both is to put SUM(Sales) on the Rows shelf twice, and then apply your table calc to only one of them.
Analysis->Percentage of->Column is equivalent to a table calc of type "Percent of Total" defined to compute the total within "Table (Down)".
Then you'd have your numerical sales in one graph and % sales in another graph below it. Make them the size you like, and you can switch between them by simply using the scroll bar.
Uh... I did manage to recreate your "disappearing percentage" situation. I'm guessing that the Filter [Measure Names] full dialog has a slightly different domain (Measures + Table calcs) than the quick filter (just Measures). The quick filter appears to get generated from one domain, but then updated from a slightly different list - and your table calculation disappears from the list. That's kind of wonky - I'll send in a bug report to support@tableausoftware.com on that since I've got everything handy here (workbook, picture, and description).
Instead of using the scroll bar to switch between the two "views" you could just create two worksheets: one that uses Sum(Sales) and the other with % of Total measure. Then you can flip between the sheets, or put them in a dashboard and arrange them however you want.
Also, you can add this Table Calculation by:
- 1. Select Add Table Calculation on the field menu of the Sum(Sales) measure.
- 2. In the Table Calculation dialog box select Percent of Total as the Calculation type.
- 3. Select Table (Down) for how to compute the total.
- 4. Click OK
I've attached a packaged workbook with an example including the two worksheets and the dashboard. You can examine how the table calculation was created by right-clicking the Sum(Sales Total) measure on the second worksheet and selecting Edit Table Calculation.Thanks for the solutions guys, but I can't use either of them. My report is already overcrowded so adding new sheets for percentages is not an option. Dashboard is also not a solution when you have many quick filters open (too little space). One of the problems is that each sheet on the dashboard has to have its own filter (without residing to global filters).
Solution that James suggested only works when you have one measure, while I have several measures you can choose from on the quick filter (see the attached file).
Disappearing measures doesn't seem like logical behavior to me. If the table calcs would just stay in the quick filter you could present a lot of info in a compact and straightforward way.