cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Add sub totals for certian columns in large table

Hi guys

 

I have a large table in Power Query. It list products that are sold and each products has variants. In this case sizes. I would like to have my "sold" column to be summarized in the bottom for each style. But had a difficulties finding a solution.

 

This is how it looks now:

ModellSizeSoldBoughtStock
Product AA200
Product AB700
Product AC1200
Product AD1800
Product AE1700
Product AF1200
Product AG500
Product AH300
     
     
Product BA100
Product BB700
Product BC1100
Product BD1500
Product BE1400
Product BF1000
Product BG400
Product BH100
     
     
Product CA2019
Product CB4077
Product CC70124
Product CD8064
Product CE60155
Product CF2052

 

My hope is to have it look like this

ModellSizeSoldBoughtStock
Product AA200
Product AB700
Product AC1200
Product AD1800
Product AE1700
Product AF1200
Product AG500
Product AH300
Total 76  
     
Product BA100
Product BB700
Product BC1100
Product BD1500
Product BE1400
Product BF1000
Product BG400
Product BH100
Total 63  
     
Product CA2019
Product CB4077
Product CC70124
Product CD8064
Product CE60155
Product CF2052
Total 29  

 

Is it possible?

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Syndicate_Admin ,

You can try this query in excel power query editor for the source table, it is possible to achieve that but due to the 'null' value in other columns, the sort of size for each product could be different by default.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Modell", type text}, {"Size", type text}, {"Sold", Int64.Type}, {"Bought", Int64.Type}, {"Stock", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Modell"}, {{"Sold", each List.Sum([Sold]), type number}}),
    Append = Table.Combine({#"Changed Type",#"Grouped Rows"}),
    #"Sorted Rows" = Table.Sort(Append,{{"Modell", Order.Ascending}, {"Sold", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",each [Modell],each if [Size] = null and [Bought] = null and [Stock] = null then "Total" else [Modell],Replacer.ReplaceText,{"Modell"})
in
    #"Replaced Value"

1.png2.png

3.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yingjl
Community Support
Community Support

Hi @Syndicate_Admin ,

You can try this query in excel power query editor for the source table, it is possible to achieve that but due to the 'null' value in other columns, the sort of size for each product could be different by default.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Modell", type text}, {"Size", type text}, {"Sold", Int64.Type}, {"Bought", Int64.Type}, {"Stock", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Modell"}, {{"Sold", each List.Sum([Sold]), type number}}),
    Append = Table.Combine({#"Changed Type",#"Grouped Rows"}),
    #"Sorted Rows" = Table.Sort(Append,{{"Modell", Order.Ascending}, {"Sold", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",each [Modell],each if [Size] = null and [Bought] = null and [Stock] = null then "Total" else [Modell],Replacer.ReplaceText,{"Modell"})
in
    #"Replaced Value"

1.png2.png

3.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

jennratten
Responsive Resident
Responsive Resident

Hello - three different ways to accomplish this in Power Query are described in this article.  https://ssbi-blog.de/blog/technical-topics-english/3-ways-for-sums-over-columns-in-power-query/ 

I have tried this, but when i did the only sum i got was an total of all products and all sizes combined. And that is not what i need. I rly need to have them summarized product by product. And not all together. 

 

@Syndicate_Admin Why not just append just the summarized rows back to the original table and call it a day? So, start with the original table as a query. Reference this query and implement your grouping/totalling. Then Append this second table to the first table.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Fowmy
Super User
Super User

@Syndicate_Admin 

You can do it in Power BI using a table or matrix visual. Is there any reason why you need a Power Query solution?

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi, yes. I dont have access to Power BI, as of now i only have excel. It will change in the future tho. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.