Row wise sum in Matrix..Need help

[Fabric999]

Hi pals,

I need help on building Totals for each row in a matrix as shown below, for each month i have to calculate totals of individual items and display at the bottom of the matrix. Please suggest. Thanks in advance.

[Anonymous · Accepted solution]

Hi, @Fabric999 

Thanks for the reply from @Rupak_bi. You can try the following method to achieve your need.

Step1: Add totals to the original table for the Sales values corresponding to each date

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZS9TgMxEITf5epI8f/ZJYQmikSDqE4pEKJAUEHF23N2zs6td8dNTroZ7c14P2dZJn007miU8dNhOr39/K6P15eH9dfM0/WwTAbp2hTdQj0W3SE93eZ7pMfb/IB0m4q+z//4+fWxC2BZAWpYX/UNOoNlFajBKtaBGubASnQheYvT3/v3PkTge6COFPgmqCPyIp1DK16lD6L4QqgjOF5mW9nlnkIiKssBA5VlryFPRQ4Qpyw7D2nKsubJ255KNr4Eoke+AqJrj0kqOk9PdDPgKOvWDjAqA9KAonJAwukTw3ZfZYZKBjcg6HK/kDJAu5YCP+fnp3MmzGmEUHXEiCiqDmMQSNWRPGKpfQXi1GbMkKhq8QpC1SWRuGpTHESrWmyEdFVLiBCw7mRFxtp+eCXmmYUN9Z7tvomwVc/wL6sLLTLXAq1zrv8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Country = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", type text}, {"Country", type text}, {"Sales", Int64.Type}}),
    #"GroupedRows" = Table.Group(#"Changed Type", {"Date", "Product"}, {{"Total Sales", each List.Sum([Sales]), type number}}),
    NewRows1 = Table.TransformColumns(GroupedRows, {{"Total Sales", each _}}),
    NewRows2 = Table.AddColumn(NewRows1, "Country", each "Totals of each month"),
    CombinedTable = Table.Combine({Source, NewRows2}),
    #"Changed Type1" = Table.TransformColumnTypes(CombinedTable,{{"Date", type date}, {"Sales", Int64.Type}, {"Total Sales", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,{"Sales"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Total Sales"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each [Sales]+[Total Sales]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Sales", "Total Sales"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Sales"}})
in
    #"Renamed Columns"

 Steps 2: Add the new table of country and sort by Index

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Rupak_bi]

To get the totals below, you need to create another matrix table without country in the row.