Forum Discussion
DAX Code to sum columns
Very new to DAX, trying to sort out this issue to sum multiple columns and rearrange how it's presented:
The table structure that I currently have:
| Name | Product A | Product B |
| Person 1 | 1 | 2 |
| Person 2 | 1 | 1 |
Result I'm looking to get:
| Product | Sum Amount |
| Product A | 2 |
| Product B | 3 |
Would this be achieved by a combination of new measures? Any help super appreciated
Hi squizzonk,
I Started by clicking "Enter Data" and created a simple table with columns: Name, Product A, and Product B. After loading the table, navigate to Power Query Editor by selecting "Transform Data" from the Home ribbon. In the editor, select both "Product A" and "Product B" columns while holding Ctrl, then right-click and choose "Unpivot Columns". Rename the resulting "Attribute" column to "Product" and the "Value" column to "Amount". Click "Close & Apply" to return to Power BI. In the report view, insert a Table visual and drag the Product and Amount fields into the visual. Power BI will automatically summarize the Amount by each Product.
I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.
Thank you and comtinue using Microsoft Fabric Community Forum.
5 Replies
Hi squizzonk,
I Started by clicking "Enter Data" and created a simple table with columns: Name, Product A, and Product B. After loading the table, navigate to Power Query Editor by selecting "Transform Data" from the Home ribbon. In the editor, select both "Product A" and "Product B" columns while holding Ctrl, then right-click and choose "Unpivot Columns". Rename the resulting "Attribute" column to "Product" and the "Value" column to "Amount". Click "Close & Apply" to return to Power BI. In the report view, insert a Table visual and drag the Product and Amount fields into the visual. Power BI will automatically summarize the Amount by each Product.
I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.
Thank you and comtinue using Microsoft Fabric Community Forum.Thanks much!!
Think the formatting messed up
First Table
Name Product A Product B
Person1 1 2
Person2 1 1
Result looking for:
Product Sum Amount
Product A 2
Product B 3
squizzonk - You need to unpivot this data, here's an article from me on why / how: https://triangle.im/power-bi-mistake-5-why-pivoted-data-leads-to-power-bi-pain/
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
