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 across each row based on those selected columns.
So, for example, if they choose all 4 severity levels, the first row would total 10, second row would be 3, etc. But if they chose only Sev2 and Sev4 (meaning only those 2 data columns would be visible), they would get 4 in the first, 2 in the second etc. There are other values that can be calculated as well (such as year over year percent of change and so on) but those would obviously be calculations similiar to this basic one.
What formula would acheive that result?
SEV1 has no data
4 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523If 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).