GROUP BY()

What's GROUP BY():

The Group By feature in Power BI summarises data based on one or more aggregate functions. You can use the Group By feature to find the average, min, max etc. It's same as the SQL Server Power BI GroupBy function.

    However Aggregation is optional it also can be used to to make set/group of unique.

Let's see with below data.

image: Sales_Tbl  (click enlarge to read clearly)

GroupBy() function gives result as Table output, so you'll take New Table, same as the below snapshot.

image: take as 'New Table'

Syntax: after New Table,

GroupBy_Tbl=GroupBy(Table, [GroupBy_columnName],..,[New_Name], [Expression])

  GroupBy_Total_Qty_City = GROUPBY(Sales_Tbl, Sales_Tbl[City],"Total_Quantity",SUMX(Sales_Tbl, Sales_Tbl[Qty]))

Note: Group By Function need a Helper Function CURRENTGROUP() which make it differ from SUMMERIZE function(explained in next chapter, SUMMARIZE function does not need any helper function).

  GroupBy_Total_Qty_City = GROUPBY(Sales_Tbl, Sales_Tbl[City],"Total_Quantity",SUMX(CURRENTGROUP(), Sales_Tbl[Qty]))

Example: 1

Find out total quantity sold by city?

Step 1:

Step 2:

after click on 'New Table' enter the formula:

Total_Qty_City = GROUPBY(Sales_Tbl,Sales_Tbl[City],"Total_Quantity",

            SUMX(CURRENTGROUP(),Sales_Tbl[Qty]))

Here in above formula notice "Total_Quantity" has been given as new name for city column in Output

And after entering the formula and press Enter, you'll find the below output city wise Total Quantity.

Example: 2

Find out total quantity sold by city and item?

Again follow the Step 1 as above

use the below formula:

Total_Qty_City_Itm = GROUPBY(Sales_Tbl,Sales_Tbl[City],Sales_Tbl[Item],

        "Total_Quantity", SUMX(CURRENTGROUP(),Sales_Tbl[Qty]))

Note: "Total_Qty" inside the formula has been used as output column name for Qty summed up as "Total_Qty"

Example: 3

Just give me output of Item name as unique using the GroupBy Function

Solution: 

again follow the Step 1 as last previously, and put below formula, and you'll notice that without aggregation function(like max, count, sum etc) can be used.

GroupBy_Item = GROUPBY(Sales_Tbl,Sales_Tbl[Item])

Example: 4

Just give me output of Item and City name as unique using the GroupBy Function

Solution: 

Just use the below formula same as in Question 3 previously.

GroupBy_Item = GROUPBY(Sales_Tbl,Sales_Tbl[Item], Sales_Tbl[City])

Click link to download the file: Click me!

If you find this was Helpful, Please subscribe our channel 

https://www.youtube.com/@KumarAnalytic