How to sum multiple rows from another table

[jk8979356]

I have a table that has [Subsidiary] [Category] [2019 Sales] [2020 Sales] columns with thousands of rows and I need to either sum the 2019 & 2029 sales rows in this table or in a new reference table so that the output is

 [Subsidiary] [Category][2019 Sales][2020 Sales]

{Sub1}{Cat1}{19 sales total}{20 sales total}

{Sub1}{Cat2}{19 sales total}{20 sales total}

{Sub2}{Cat1}{19 sales total}{20 sales total}

{Sub2}{Cat2}{19 sales total}{20 sales total}

Where each row sales total is the total for only that specific subsidiary>category combo. What is the cost to do this?

[edhans]

Can you provide some sample data @jk8979356 per the links below? For a rough overview on the direction to go, see this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYlMgtlSK1YlWSgKyjKAi5uZgoWQg0xiIzYDYAiySAmQVgRSAFcbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subsidiary = _t, Category = _t, #"2019 Sales" = _t, #"2020 Sales" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"2019 Sales", Int64.Type}, {"2020 Sales", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Subsidiary", "Category"}, "Attribute", "Value"),
    #"Extracted First Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.Start(_, 4), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted First Characters",{{"Attribute", "Year"}, {"Value", "Sales"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}})
in
    #"Changed Type1"

Turns this:

into this:

Which is where @MattAllington was going as far as properly modeling your data before bringing it into the Data Model. Now you can connect that to a Date Table and do all sorts of things easily.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[MattAllington]

I'm not sure what you are asking, but the better way to shape your data is to load a table that looks like this

Subsidiary, Category, Year, Sales

with a table structured like this, it is easy to build summary matrix visual by dragging columns

Subsidiary, Category, to the rows of the matrix

Year, to the column

Sales to the values