SUM different row

[Anonymous]

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"))

[Anonymous · Accepted solution]

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 !

[dax]

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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Zoe,

I will. I'm still learning PBI and your answers helped me a lot for understanding M mechanism, thank you so much for this !

Have a nice day/evening !

[dax]

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.

[Anonymous]

Quick question, do you want the summed up data listed in the table itself or during runtime in a visual?

[Anonymous]

Hi epappu,

In the table itself.

Visual function are used for sum only, the issue is that I need to use some soustraction and divide formula as well.

Thanks

[Anonymous]

Hi Sabri - 

I tried to do a similar dataset in a local pbi file, and please see the screengrab below. I think this will help you get your totals irrespective of how many rows you have starting with A or B.

If this helped you, please accept this as solution.

Regards

[Anonymous]

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

[Anonymous]

Hi Sabri,

Yes, it should work if you change the formula like this...

Regards

[Anonymous]

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 !

[dax]

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 Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.