Recently a friend of mine asked me for help with a problem that many of you may have encountered. She was analyzing data from a survey. One of the questions on the survey was, "Out of these 50 movies, which one is your favorite?"
In her analysis she wanted to look at the top 10 movies based on number of respondents who chose it as their favorite. Then she wanted to see the percent of all respondents who chose each of these 10 as a favorite. For example, Shark Tales may be the number 3 favorite movie with 6% of all respondents choosing it as a favorite. Our first inclination was to create a filter that only includes the top 10 based on number of respondents, and then use a table calculation to compute the percent of total. The problem there is that table calculations are, like the name says, based on the values shown in the table--not the underlying data. The table calculation computed how many people chose each movie out of the people who chose one of those top 10 movies, not all respondents.
So we had to get a little more tricky by creating a new calculated field.
Here's how it works
- Let's say the view shows Movie Title on the Rows shelf and Count(Number of Records) on the columns shelf.
- First filter the move title field to show the top 10 by Count(Number of Records). You can do a top filter by dragging the field to the Filter shelf and then switch to the Top tab.
- Now instead of using a table calculation to get the percent of total, create a new calculated field by right-clicking the data window and selecting Create Calculated Field.
- Give the calculated field a name (something like Percent of Total).
- Type a formula like the one shown below (using your actual field and table names of course):
COUNT([Number of Records])/RAWSQL_INT('Select COUNT(*) From "SHEET1$"')
This formula uses a RAWSQL function, which means that the specified SQL query is simply passed to the data source and Tableau doesn't try to interpret it at all. You can probably figure out that the sql query in the above formula returns an integer that is simply a count of all the records. I should mention that the RAWSQL functions are not supported by local file data sources such as Excel and Text files. You can extract your data
(select Data > Extract) from Excel or Text files so you can use these types of functions.
- Click the Check Formula button to make sure the calculation is valid and then click OK.
- Now, use the new field you have just created as the measure in your view. So the view would have Movie Title on the Rows shelf and AGG(Percent of Total) on the Columns shelf.
- Right-click the axis and select Format.
- Make sure the Axis tab is selected and then select Percentage as the Number format.
I hope you find this useful. I've attached a packaged workbook with an example. You could use the same technique for any time you want to see the percent of total using the underlying data instead of the values shown in the table.