I have a pivot table that has a category column field (with categories of 15, 25, 45, and 65), a Status field (High, Medium, Low), and a Code field (X1, Y1, Z1, L1, etc.). The codes always match up to the same status (so L1 is always Medium, for example), so that data is somewhat redundant. At the intersection is simply a sum of values. So the table would look like this:
<tbody>
</tbody>
What I need to do that is calculate at the category level a ratio of (sum of all non-Low numbers) / (total of all status numbers for that category). So for Category 15, the ratio would be 3 / 3 (or 100%), because there are no Lows. For Category 25, it would be (2+8)/(2+8+1), or 10/11, or 91%. In essence it's just telling us for each category what the success level of that row is (a success being non-Low). For Category 45 it would be 8 / (8 + 9), or 8/17, and so on.
I'm really struggling with how to make this happen in a pivot table. I tried a calculated item, and it locked up Excel. I'd love to do a calculated field or formula, but I'm not sure how to do that, as it almost has to be like a SUMIF to check for the Low. Any suggestions?
Category | 15 | 25 | 45 | 65 | |||
Status | High | High | Med | Low | Med | Low | Med |
Code | X1 | Z1 | L1 | P1 | Y1 | J1 | L1 |
| Row1 | 3 | 2 | 8 | 1 | 8 | 9 | 1 |
| Row2 | 1 | 3 | 4 | 5 | 0 | 0 | 1 |
| Row3 | 2 | 0 | 0 | 1 | 6 | 5 | 0 |
<tbody>
</tbody>
What I need to do that is calculate at the category level a ratio of (sum of all non-Low numbers) / (total of all status numbers for that category). So for Category 15, the ratio would be 3 / 3 (or 100%), because there are no Lows. For Category 25, it would be (2+8)/(2+8+1), or 10/11, or 91%. In essence it's just telling us for each category what the success level of that row is (a success being non-Low). For Category 45 it would be 8 / (8 + 9), or 8/17, and so on.
I'm really struggling with how to make this happen in a pivot table. I tried a calculated item, and it locked up Excel. I'd love to do a calculated field or formula, but I'm not sure how to do that, as it almost has to be like a SUMIF to check for the Low. Any suggestions?
