Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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 !

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Sabri,

 

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

 


Regards

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 ?

 

Col1MonthValueTotal
External Revenue USJan100 
External Revenue USFeb150 
Internal Revenue USJan200 
Internal Revenue USFeb250 
External Revenue GBPJan300 
External Revenue GBPFeb350 
Internal Revenue GBPJan400 
Internal Revenue GBPFeb450 
Global Revenue USJan  
Global Revenue USFeb  
Global Revenue GBPJan  
Global Revenue GBPFeb  

 

Sorry to bother you but I'm still learning the logic behind Dax language. Thanks again !

  • dax's avatar
    dax
    Community Support
    6 years ago

    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.