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 | 300 | |
| B2 | 400 | |
| A.Total | ||
| B.Total |
And I need to calculate the Total column as follow:
A.Total = Value[A1] + Value[A2]
B.Total = Value[B1] + Value[B2]
final result:
| Col1 | Value | Total |
| A1 | 100 | |
| A2 | 200 | |
| B1 | 300 | |
| B2 | 400 | |
| A.Total | 300 | |
| B.Total | 700 |
Im not sure if I should use a measure or a calculated column for this calculation, for now: I'm trying with a Custom Column, Can you please advise if this script is correct ?
Calculte(SUM(["A1"]+["A2"]),FILTER(["Total"]="A.Total"))
and
Calculte(SUM(["B1"]+["B2"]),FILTER(["Total"]="B.Total"))
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 !
11 Replies
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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,
I am sorry for my solution not helping you and I accept it as solution(it might just suitable for my scenario), if this is not the solution, you coud unmark this case. By the way, it seems that you solved this problem, if so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
