Forum Discussion
SUM different row
Hi, I'm a beginner in Power BI and I have (I think?) a simple question: I have currently this table (Total is a calculated column) Col1 Value Total A1 100 A2 200 B1 30...
Hi,
Sorry for the late reply, I was in vacation.
I'm not sure if this is the right method by the admin to mark a reply as solved, that's definetly NOT the solution that I was looking for. Having admin rights doesn't mean you can do whatever you want...
The right way that I did is:
1) Pivot those rows into columns in Power BI Queries with language M.
2) Create a calculated column to sum up the new pivoted columns.
3) Unpivot those columns into rows.
This wroked great !
Hi Sabri,
You could try to achieve this in M code like below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyNDBQitUBcoyAHCMoxwkkYwzjgGRMQJxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Value", Int64.Type}}),
#"Inserted First Characters" = Table.AddColumn(#"Changed Type", "First Characters", each Text.Start([Col1], 1), type text),
#"Grouped Rows" = Table.Group(#"Inserted First Characters", {"First Characters"}, {{"sumtotal", each List.Sum([Value]), type number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Col1", each [First Characters] & "'s sum total"),
#"Appended Query" = Table.Combine({#"Inserted First Characters", #"Added Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Appended Query",{"First Characters"})
in
#"Removed Columns"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.