CODEWITHSUNDEEP
tableau-api
Ashish B Sathish
Ashish B Sathish

Reputation: 121

Calculate the percentage of a column which has redundant rows in Tableau

I want to calculate the percentage of a column which has redundant rows. For example ... I would like to calculate the percentage of "Success" for A and B in the below table

+-------+---------+
| Name  | Result  |
+-------+---------+
| A     | Success |
| B     | Success |
| A     | Fail    |
| A     | Success |
| B     | Fail    |
| B     | Fail    |
| A     | Success |
+-------+---------+

I tried using Calculated field by putting If([NAME]) = "Success" then 1 else 0 and then editing the table calculation to Percentage -> table across and down... but didn't work :(

Upvotes: 0

Views: 601

Answers (1)

Andrew LaPrise
Andrew LaPrise

Reputation: 3423

You can absolutely use the "Percent of Total" table calculation for this. The tricky bit is going into the "Edit Table Calculation" dialog and telling Tableau how you want it to perform the calculation.

Here's an example of how to do this that you can adjust to fit your specific needs. Place [Name] and [Result] in the Rows shelf. Then place SUM(Number of Records) into Text. You'll end up with something like this:

 Name      Result    |     |
---------------------+-----+
 A         Fail      |   1 |
           Success   |   3 |
 --------------------+-----+
 B         Fail      |   2 |
           Success   |   1 |
 --------------------+-----+

Then right click on SUM(Number of Records) and click "Add Table Calculation...". At the top of the Table Calculation dialog, go to "Calculation Type:" and choose "Percent of Total". In "Summarize the values from:", it will default to "Table (Down)". Go ahead and hit Apply at this point and see what happens. Bad news - it's wrong.

 Name      Result    |     |
---------------------+-----+
 A         Fail      | 14% |
           Success   | 43% |
 --------------------+-----+
 B         Fail      | 29% |
           Success   | 14% |
 --------------------+-----+

The default "Table (Down)" is almost never what you actually want. That says to calculate the percent of total for your entire partition, but you'd actually like to see that percent of total for each Name.

Until you get really good at this part (and maybe even after you've become a Tableau Zen Master and a Tableau god among men), I recommend always going to the advanced menu when you're defining your table calculations. It's a good opportunity to really think through exactly how you want Tableau to perform the calculation. In this case, you want to calculate the percent of each result (Success and Fail) for each name.

Go to the Advanced dialog (under "Summarize the values from:"). You'll see Name and Result under Partitioning and nothing under Addressing. Move Result over to Addressing and leave Name under Partitioning. What you're saying here is "I want Tableau to calculate the percent of each result (Success or Failure). I want it to do this for each name."

Apply those changes, and you should see something like this:

 Name      Result    |     |
---------------------+-----+
 A         Fail      | 25% |
           Success   | 75% |
 --------------------+-----+
 B         Fail      | 67% |
           Success   | 33% |
 --------------------+-----+

Perfect. If you just want to see the Successes, just right click on "Fail" in the table and click Hide. Do NOT filter them out. That will remove those rows from your partition, and thus from the total that is considered in the percent of total calculation. By hiding the Fails instead of filtering them, you keep them in the partition but don't show them in the data view.

Upvotes: 2

Related Questions