Adding total to the yearly revenues

[magikan01]

Hi everyone!

So, I'm new to Power BI - literally one week into my course, and already am feeling it. Anyway, can any of you help me out on this please? 

I have a table of total revenues from Excel, picture below:

Problem is I've been having trouble with putting the Total Revenues column inside the Power query editor. 

I tried grouping the years to get a sum total, and so far it just gives me errors. 

I feel stuck. Any tips?

Much appreciated. 

[v-xinruzhu-msft · Accepted solution]

Hi @magikan01 

1.The better way is to use the marix visual or table visual it will generate the total, e.g use matrix visual.

put the following field to matrix

Output

2.You can add three custom columns to calculate the year total, you can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NZDNagMxDITfxecc9GNZ0nGbbAOhTULbFMqS93+NSvIGDB78jcZjb1u7LNd2aLFQRmcIQTBQoD0PW3tf316QhXvCjs5e8HP5mpDdSShEH0NpFFzuCdEGuKclULe0ICvhPv9XM0aOIWKf94uowbz/8shyAuyYw6KoTHUytOtu+cgUBuyT+IyToSwyuzzO1dJcOAlFzSwlPCji0vK93tMCYq4pzNCg6qLtltvxp1KUbSSheJlOATK7XG+/9dbe51951OXKJaBpOa3HJMxMSQhVSEo4a1ie/w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, #"2021" = _t, #"2022" = _t, #"2023" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"2021", Int64.Type}, {"2022", Int64.Type}, {"2023", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sum_2021", each List.Sum(#"Changed Type"[2021])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Sum_2022", each List.Sum(#"Changed Type"[2022])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Sum_2023", each List.Sum(#"Changed Type"[2023]))
in
    #"Added Custom2"

Output

Best Regards!

Yolo Zhu

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

[magikan01]

forgot to mention, I did deselect the total in the month part, just wanted to show it for the question purposes.