topic how to summarize several columns into a new table in DAX Commands and Tips

how to summarize several columns into a new table

Anonymous — Tue, 19 Jan 2021 00:32:14 GMT

I have data in in this format:

column1, column2, column3

0, 1, 1

1, 1, 1

1, 0, 1

How can I summarize the data into a new table where the first

column contains the column name from above,

and the second column contains the sum for each column above:

column1, 3

column2, 2

column3, 4

Re: how to summarize several columns into a new table

mahoneypat — Tue, 19 Jan 2021 00:45:40 GMT

This is most easily done in the query editor with an unpivot followed by a group by step with Sum aggregations. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMDpVidaCCJzIOw0HmGKCqBvFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Column"}}), #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Column"}, {{"ColumnSum", each List.Sum([Value]), type number}}) in #"Grouped Rows"

Pat

Re: how to summarize several columns into a new table

lbendlin — Tue, 19 Jan 2021 00:51:31 GMT

unpivot your table, then group by attribute with aggregation type sum for the value

Re: how to summarize several columns into a new table

Anonymous — Tue, 19 Jan 2021 01:09:30 GMT

lbendlin -- thanks for the note!

I started by importing data using "Get Data" in Power BI...so at this point,

I would need to start with Power Pivot, perform the group by, then import

that data into Power BI...correct? THANKS!

Re: how to summarize several columns into a new table

Anonymous — Tue, 19 Jan 2021 01:16:43 GMT

mahoneypat -- thanks for the code!

Unfortunately I'm el-newbo-grande with M code -- how would I integrate this code with an actual table? Thanks!

Re: how to summarize several columns into a new table

mahoneypat — Tue, 19 Jan 2021 01:59:45 GMT

Those steps are all from the Ribbon (no custom M). Here are the steps

- Connect to your data so you have the initial table in power query

- Change the data types of the columns if needed

- Highlight all 3 columns, right click and choose Unpivot Columns

- Rename the Attribute Column to "Column" (or whatever you want)

- Highlight that column and click on Group By on the Home tab of ribbon

- In the pop up, give the new column a name, choose Operation of Sum on the Value column

Pat

Re: how to summarize several columns into a new table

Anonymous — Tue, 19 Jan 2021 05:12:11 GMT

Hello Anonymous ,
Are you looking for this then please try the below calculation for the new table.

Summarize table =

SUMMARIZE(

'Summarize',

"Column 1", SUM('Summarize'[Column1]),

"Column 2", SUM('Summarize'[Column2]),

"Column 3", SUM('Summarize'[Column3])

)

Re: how to summarize several columns into a new table

Anonymous — Tue, 19 Jan 2021 15:21:56 GMT

Hello TarunSharma – thanks, this produces a table that looks like this:

col1, col2, col3

1 2 3

I need a table that looks like this

col1, 1

col2, 2

col3, 3

Re: how to summarize several columns into a new table

Anonymous — Tue, 19 Jan 2021 16:26:15 GMT

mahoneypat -- Thanks, Pat...I was able to follow your directions -- table created!