Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum data to new rows in same table

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 r...
Need Help
  • dax's avatar
    dax
    6 years ago

    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's avatar
amitchandak
Super User
6 years ago

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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    dax
    Community Support
    6 years ago

    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.