Forum Discussion
Sum a row using visible columns
I have created a table that allows a user to choose what data columns they wish to see (via field parameters). There are up to 4 possible values they can select from. I need to get a dynamic total ...
SEV1 has no data
If the user selected all 4 severity levels, the goal is for the Total Count column to equal this:
| Business Unit | SEV1 CYTD | SEV2 CYTD | SEV3 CYTD | SEV4 CYTD | Total Count |
| Unit 1 | 6 | 4 | 10 | ||
| Unit 2 | 1 | 2 | 3 | ||
| Unit 3 | 2 | 9 | 10 | 21 | |
| Unit 4 | 2 | 2 | 4 | ||
| Unit 5 | 1 | 9 | 5 | 15 | |
| Unit 6 | 1 | 1 | |||
| Unit 7 | 4 | 19 | 3 | 26 | |
| Unit 8 | 3 | 3 |
If the user only selected columns 2 & 4, for example, the result would be this:
| Business Unit | SEV2 CYTD | SEV4 CYTD | Total Count |
| Unit 1 | 4 | 4 | |
| Unit 2 | 2 | 2 | |
| Unit 3 | 2 | 10 | 12 |
| Unit 4 | 2 | 2 | |
| Unit 5 | 1 | 5 | 6 |
| Unit 6 | 0 | ||
| Unit 7 | 4 | 3 | 7 |
| Unit 8 | 0 |
If they selected 1 & 2, it would be this:
| Business Unit | SEV1 CYTD | SEV2 CYTD | Total Count |
| Unit 3 | 2 | 2 | |
| Unit 5 | 1 | 1 | |
| Unit 7 | 4 | 4 |
My field parameters slicer already takes care of the columns and rows to display. What I need is a dynamic formula that will calculate the sum across each row based on what columns (severity levels) the user selects (or is made visible).
Note: The values I'm using in the field parameters are measures. They do not directly come from a table.