Sum many column values from each row using DAX

Question

Say I have the table

[3 0 2 5 8 1
 2 9 8 3 1 2
 9 8 3 2 3 1]

All I want is a function to yield the column

[19
 25
 26]

without having to type out literally every single column name. My actual data matrix is 30 columns long.

Is there a way to do this that I'm missing? Or is DAX just completely incapable of doing this? I've Googled every single possible combination of the words "sum", "row", "-column", "PowerBI", and "DAX" and gotten absolutely nothing useful. Why in the world would SUM and SUMX automatically assume that the user would always in every single instance ONLY want to sum along dimension 1?

Do you mind sharing what attributes those rows/columns are? In this case I think you can transpose/pivot the table to make it work. — Foxan Ng
– Foxan Ng, Commented Mar 28, 2017 at 5:16

Community · Accepted Answer · 2017-05-23 12:32:15Z

2

This is not easily done in DAX, as far as I know. Your only options are to spell out all the columns, or drop that requirement and follow the instructions in my earlier answer on how to do this with the Query Editor (i.e. PowerQuery).

Your "why" questions are possibly just uttering of (quite understandable) frustration, but trying to answer them nonetheless: I think the basic idea of tabular reporting is that you aggregate over rows, not columns, and all nearly operations are centered around those. In PowerBI (and many other reporting tools) you need to (un)pivot your data first to do the type of query you're after, which comes full circle to abovelinked solution.

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Mar 28, 2017 at 6:30

Jeroen

64.4k47 gold badges231 silver badges372 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum many column values from each row using DAX

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related