In Power BI Desktop we can group the values of different row into a single row with respect to any particular column.
Group By in Power BI desktop works similar way to Group By in SQL, we are getting a new resultant table as an output of Group By functionality in both the places.
In today’s example we are going to create group for Quantity field for different rows with respect to Order date.
In Power BI we have two ways to add Group By:
- Without using DAX formula (In Query editor)
- With using DAX formula
First, I will explain how to use Group by in Query Editor:
Load Sample Super store in Power BI Desktop:
Click on Edit Queries from Home menu:
It will open Power Query Editor window.
We have two places from where we can select Group By functionality in Power Query Editor window:
- Home menu
- Transform menu
Now we want to use Group by for Order date and want to calculate sum of quantity on different order date.
Select Group By option >
We will get a Group By window
Select “Order Date” in Group By
Give New column name ,Select Operation (select Sum) and Quantity from Column:
Our new table is present as a result of Group By. And two column are present Order date and respectice sum of quantity for different order date in table. So we have grouped Quantiry rows w.r.t Order date.
Change Name for Query and give it ”Group by table Query Editor”
Apply Ascending Sorting on Order date column:
So “Group by table Query Editor” is the result of Group By with out writing any DAX formula only using query editor user interface.
Now let’s see how we can use Group By function using DAX in Power BI Desktop:
Open Power BI Desktop > Data view:
Select Modelling> New Table
By selecting New Table, we are adding a new table from DAX equation, derived from existing table not derived from data base.
Select new table, by default name is Table, provide a table name (Sum of Qty using DAX) and name of column, for which we want to apply Group By (Order date). Now select GROUPBY function and complete the syntax for function.
Sum of Qty using DAX = GROUPBY (Orders,Orders[Order Date],“Sum of Qty DAX”,SUMX(CURRENTGROUP(),Orders[Quantity]))
We get a resultant new table with our result:
Select ascending for Order date column:
Our both resultant tables (Using Query editor & Using DAX function) are having same data.
This was all about grouping rows in Power BI using Group By functionality. If you prefer watching Video you can watch the step by step that we have done above in the video below . Please don’t forget to subscribe to the Channel if you don’t want to miss the latest in Data Visualization , Data Analytics , Data Science & ML DL AI World : www.YouTube.com/c/InstrovateTechnologies