Forum Discussion
Matrix_ Help
Hi,
I have below given sample data file. I just want to summarize those as given below. could you please help me to do it power bi desktop.
Thank you.
in order to do that the rows & column items have to be in rows in the table
you can achieve that in 2 ways:
1) unpivoting your original table in query editor, e.g. like this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBLCoUwDAXQvWSs0KT1N9T3/LuD4v63obF6Rw2UQMMhNyRG6qkgcdyWju93f+pGi3YDnUWkAUASYPGo4hP6AfmEfKUdfsbwQ/4g4SVBzVeUjCCVNWUCqfPrzgCNve4C1FpBK0iXD9o+wM4O2oHYCjpAJHuX8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Client = _t, Month = _t, #"Total Charge" = _t, #"Total Payment" = _t, #"Total Balance" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Month", type date}, {"Total Charge", Int64.Type}, {"Total Payment", Int64.Type}, {"Total Balance", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Client", "Month"}, "Total", "Value") in #"Unpivoted Columns"then you have one measure which is just
Measure= SUM(Table[Value])
2) create a new table with a row for each column you wanna sum
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScxRcM5ILEpPVYrVgQkEJFbmpuaVIIk4JeYk5iUD1cQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Total = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Total", type text}}) in #"Changed Type"
and then use this measureMeasure 2 =
SWITCH(
SELECTEDVALUE(RowsTable[Total]),
"Total Balance", SUM('Table'[Total Balance]),
"Total Charge", SUM('Table'[Total Charge]),
"Total Payment", SUM('Table'[Total Payment]),
BLANK()
)
1 Reply
- StachuCommunity Champion
in order to do that the rows & column items have to be in rows in the table
you can achieve that in 2 ways:
1) unpivoting your original table in query editor, e.g. like this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBLCoUwDAXQvWSs0KT1N9T3/LuD4v63obF6Rw2UQMMhNyRG6qkgcdyWju93f+pGi3YDnUWkAUASYPGo4hP6AfmEfKUdfsbwQ/4g4SVBzVeUjCCVNWUCqfPrzgCNve4C1FpBK0iXD9o+wM4O2oHYCjpAJHuX8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Client = _t, Month = _t, #"Total Charge" = _t, #"Total Payment" = _t, #"Total Balance" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Month", type date}, {"Total Charge", Int64.Type}, {"Total Payment", Int64.Type}, {"Total Balance", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Client", "Month"}, "Total", "Value") in #"Unpivoted Columns"then you have one measure which is just
Measure= SUM(Table[Value])
2) create a new table with a row for each column you wanna sum
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScxRcM5ILEpPVYrVgQkEJFbmpuaVIIk4JeYk5iUD1cQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Total = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Total", type text}}) in #"Changed Type"
and then use this measureMeasure 2 =
SWITCH(
SELECTEDVALUE(RowsTable[Total]),
"Total Balance", SUM('Table'[Total Balance]),
"Total Charge", SUM('Table'[Total Charge]),
"Total Payment", SUM('Table'[Total Payment]),
BLANK()
)