Forum Discussion
SUM different row
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 !
Thank you for your reply, the table of my OP was an exemple, if I change the name as below, I'm sure if this would work ?
| Col1 | Value | Total |
| External Revenue US | 100 | |
| Internal Revenue US | 200 | |
| External Revenue GBP | 300 | |
| Internal Revenue GBP | 400 | |
| Global Revenue US | ||
| Global Revenue GBP |
Hi Sabri,
Yes, it should work if you change the formula like this...
Regards
Hi Epappu,
My mistake, yes it works. However I noticed if I add a month column as below, It will sum up all the months. Is there a way to calculate by month ? a FILTER inside another filter ?
Col1 Month Value Total External Revenue US Jan 100 External Revenue US Feb 150 Internal Revenue US Jan 200 Internal Revenue US Feb 250 External Revenue GBP Jan 300 External Revenue GBP Feb 350 Internal Revenue GBP Jan 400 Internal Revenue GBP Feb 450 Global Revenue US Jan Global Revenue US Feb Global Revenue GBP Jan Global Revenue GBP Feb Sorry to bother you but I'm still learning the logic behind Dax language. Thanks again !
Hi Sabri,
It seems that you change the requirement, you could try to change M code like below
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PMVTSUfLNzyvJANJhiTmlqUqxOtFKrhUlqUV5iTkKQallqXmlqQqhwUB5r8Q8IGloYIBHjVtqEkiNKUSNZx5uc4wM8KmBmGNkisMud6cAuEHGuBwEUQQxyRiXi5BNMsHlJGSTTEAmxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Value = _t, Column1 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Col1", type text}, {"Value", Int64.Type}}), #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([Col1], " ", {0, RelativePosition.FromEnd}), type text), #"Grouped Rows" = Table.Group(#"Inserted Text After Delimiter", {"Month", "Text After Delimiter"}, {{"sumtotal", each List.Sum([Value]), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Col1", each [Text After Delimiter]&" "& [Month]&"'s sum total"), #"Appended Query" = Table.Combine({#"Inserted Text After Delimiter", #"Added Custom"}), #"Removed Columns1" = Table.RemoveColumns(#"Appended Query",{"Text After Delimiter"}) in #"Removed Columns1"Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
