topic Re: Sum a row using visible columns in DAX Commands and Tips

Sum a row using visible columns

Anonymous — Mon, 10 Feb 2025 17:04:56 GMT

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?

Re: Sum a row using visible columns

lbendlin — Mon, 10 Feb 2025 20:45:08 GMT

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/1447523

Re: Sum a row using visible columns

Anonymous — Mon, 10 Feb 2025 21:05:11 GMT

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).

Re: Sum a row using visible columns

Anonymous — Mon, 10 Feb 2025 22:06:00 GMT

Note: The values I'm using in the field parameters are measures. They do not directly come from a table.

Re: Sum a row using visible columns

lbendlin — Mon, 10 Feb 2025 23:20:35 GMT

SEV1 has no data