Sum data to new rows in same table

[Anonymous]

Hi all,

Would appreciate some guidance on an challenge I have. Basically I'm working with some sales data that shows global countries, but for the US shows each State split out with no Total US row. My data looks something like the below without the rows in red.

What I would like to do is append to the same table rows that represent total US per product. I have a dozen or so columns with values that each need to be summed (I've just included 3 below for illustration). Basically I want to generate the rows in red.

I'd rather not split the data into another table, as I have data in this format in multiple tables already for different years.

Market	Product	Sales Value	Sales Volume	COGS
Italy	X	10	9	(3)
Michigan	Y	5	7	(1)
New Jersey	Z	14	4	(5)
Ohio	Y	5	6	(6)
Spain	Z	15	10	(7)
Texas	Z	8	5	(4)
US	Y	10	13	(7)
US	Z	22	9	(9)

Any help is appreciated!

[dax · Accepted solution]

Hi lavelle72, 

You could refer to my sample for details(create a table for reference).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzKlU0lGKAGJDAyBhCcQaxppKsTrRSr6ZyRmZ6Yl5QKFIIDYFYnOQtCFE2i+1XMErtag4FWRAFMgAEyABwhqmEBX+GZn5SJrNQFJmEKnggsTMPJg+U5jtGuYQ2ZDUisRiqKwFVLeGCVAuFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Market = _t, Product = _t, #"Sales Value" = _t, #"Sales Volume" = _t, COGS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Product", type text}, {"Sales Value", Int64.Type}, {"Sales Volume", Int64.Type}, {"COGS", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Market"}, #"Table (2)", {"Market"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"country"}, {"Table (2).country"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table (2)", {"Table (2).country", "Product"}, {{"Sales Value", each List.Sum([Sales Value]), type number}, {"Sales Volume", each List.Sum([Sales Volume]), type number}, {"COGS", each List.Sum([COGS]), type number}, {"Market", each List.Min([#"Table (2).country"]), type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([#"Table (2).country"] = "US")),
    #"Appended Query" = Table.Combine({#"Filtered Rows1", #"Expanded Table (2)"}),
    #"Removed Columns" = Table.RemoveColumns(#"Appended Query",{"Table (2).country"})
in
    #"Removed Columns"

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.

[amitchandak]

You can one additional column that can mark it US Data of not, let say US Flag. You can have a new column like

new country = if([us flag] ="Y", "US", [country]), You can use this one for display.

You can also explore Union and summarize option

https://docs.microsoft.com/en-us/dax/union-function-dax

https://docs.microsoft.com/en-us/dax/summarize-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Thanks for the suggestion Amit.

The flag idea seemed the simplest so I have been playing around with that, per your suggestion. 

The issue I face now is that for each product, I see multiple rows for New Country = US (makes sense as each row previously was a State). Now when I use New Country for display, all metrics get summed together - this is fine for some, but not for others which should be weighted averages.

I am going to have a closer look at the SUMMARIZE and UNION option to see if that will work better.

[dax]

Hi lavelle72, 

Did you try the M code? Will this works or not? By the way, you also could inform me some of your sample data, then I will help you more correctly.

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.

[AnkitBI]

Hi @Anonymous 

Can you share your sample data. Primarily what you can do.

1) In Power Query, use Group By to summarise data at country level for US and then append with your base table OR

2) In DAX, create a new table using SUMMARIZE function to summarise data at country level for US and then use UNION to append with your base table

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.