Here we will see two examples on Power bi sum group by multiple columns and Power bi sum group by two columns.
How power bi sum group by multiple columns
Here we will see Power bi sum and group by multiple columns in power bi.
Here is the sample data we are going to use to solve this problem.
Here we will combine column1 by commas and sum the column2 based on column 3. So, it will show A,B,C(column1) 114(column2) XX(column3)
For this, we will create a calculated table by using Power Bi Dax.
- To create a new table, Click on the Modelling -> New table.
- Then write the DAX formula:
Combined = SUMMARIZE ( 'Table', 'Table'[Column3], "Column1", CONCATENATEX ( 'Table', 'Table'[Column1], "," ), "Column2", SUM ( 'Table'[Column2] ) )
In the below screenshot, you can see the power bi sum group by multiple columns.
Power bi sum group by two columns
Here we will see the Power bi sum group by two columns using power query in power bi desktop.
We will use the below sample data to find the sum group by two columns in power bi desktop.
We will sum up the values based on WO# and group them by serial using power query.
- In Power bi desktop click on the Transform Data from the ribbon, to open power query editor.
- In power query editor, Click on Transform tab -> Group by.
- Now Group By window will open, then select the Advanced option.
- From the dropdown select Serial. Next click on Add Grouping.
- Then from the dropdown select WO#.
- Change the name of the column to SumOfValue, by default it is count.
- Then change the aggregation to Sum from the dropdown list.
- Select the aggregation column, here it is value column.
- Click on OK.
In the below screenshot you can see the power bi sum group by two columns using power query
You may also like the following Power BI tutorials:
- Power bi Date Difference
- Power bi date format
- Power bi group by date range
- Power BI Date Hierarchy
- How to create a table in Power bi dashboard
- Power bi show items with no data
- Power BI Measure Date + Examples
- Power BI DAX SUM and SUMX function
In this power bi tutorial, we learned power bi sum group by multiple columns. And also we discussed the below points power bi sum group by two columns using power query.
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com