Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use the Advanced Group in Power BI Power Query to calculate the Total by Region and Item Type. Let’s get started
The caption below shows the data already loaded to the Power BI Power Query
In the Table group of the Transform tab, click on Group By
In the Group By dialogue box, check Advanced
The first and second column to Group By is Region and Item Type respectively.
Click on Add grouping and select Item Type from the drop-down
Type in Total by Region & Item Type as the new column name
Select SUM from the Operation drop-down
Select Total from the Column drop-down
Click on Add aggregation
Type in Average by Region & Item Type as the new column name
Select AVERAGE from the Operation drop-down
Select Total from the Column drop-down
Click OK
From the caption above, we can see the report of the multiple Group By criteria specified