Forum Discussion

nixoniraj's avatar
nixoniraj
Frequent Visitor
3 years ago

Calculate Column Total for multiple columns

Dear Team,

This query is for Power Query Editor in Power BI

 

I have an original Table like this :

 

I want to take column total for each country at last part of the table (ie, after 9. Sep) like this :

 

Anyhelp would be much appreciated for showing stepwise process.

 

Need Help

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi nixoniraj ,

     

    You just need to use Table.InsertRows with List.Sum functions on the country columns:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtRT8ErMU9JRcq0oyC8qKQayTA0MgKQFkIzViVYy0lNwS01CUWBuClJgZABVYayn4JtYhKLCCKZCKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Type = _t, India = _t, China = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"India", Int64.Type}, {"China", Int64.Type}}),
    
    insertRow =
    Table.InsertRows(chgTypes, Table.RowCount(chgTypes),
        {
            [
                Month = "Grand Total",
                Type = null,
                India = List.Sum(chgTypes[India]),
                China = List.Sum(chgTypes[China])
            ]
        }
    )
in
    insertRow

     

    Output:

     

    Please note, however, that I would strongly recommend against using Power Query for this sort of thing. It's really not what it's designed for.

     

    Pete