Forum Discussion
Summing line data into a table format by category
I need to find a way to pull individual columns out and sum them up by category. I have included a simplified version of what I am trying to do. Anyone have any ideas on how to do this?
Data set
| Product1Sold | Product1Inventory | Product2Sold | Product2Inventory | Product3Sold | Product3Inventory |
| 10 | 4 | 6 | 2 | 15 | 22 |
| 11 | 14 | 6 | 18 | 12 | 36 |
End report
| Product # | Sold | Inventory |
| Product 1 | 21 | 18 |
| Product 2 | 12 | 20 |
| Product 3 | 27 | 58 |
Hi, Anonymous
I made a sample.
Like this:
My sample is below.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.Best Regards,
Community Support Team _ Janey
2 Replies
- v-janeyg-msft
Community Support
Hi, Anonymous
I made a sample.
Like this:
My sample is below.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.Best Regards,
Community Support Team _ Janey - bcdobbs
Community Champion
Are you able to manipulate the data in power query?
If so rough steps would be:
1) Pivot the data so columns become rows.2) Add columns from selection to split Product1Sales into Product1 Sales etc.
3) Unpivot the data back so you have a row per product with 3 columns Product, Sales, Inventory
