WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn Power BI » Power BI GroupBy

Power BI GroupBy

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Group by is a type of query which is also used in power bi, there is an editor in power bi which is known as query editor window which has various queries for accessing data and such query is Group by which is used to group rows based on a given certain value.

Group By Function in Power BI

Power BI GROUPBY Dax Function will group together data by collecting the information from two different tables. This works almost similar to the summarized function in Power BI, but groupby doesn’t allow us to do any kind of implicit calculations by using the calculate function; instead, it allows us to use the new DAX function “CURRENTGROUP()” inside the aggregation functions in the additional column it adds.

Below is the SYNTAX of the Power BI GROUP BY Dax function.

Power BI Group By Syntax

  • <Table>: First, we need to specify the table name from where we are grouping the data.
  • [<GroupBy_columnName1>]: What is the relationship first column from any of the two tables?
  • [<GroupBy_columnName2>]: What is the relationship second column from any of the two tables?
  • <Name>: This will be what is the name for the new column we are going to create.
  • <Expression>: By using which DAX function, we need to aggregate the values.

I know it is not that easy to understand by reading the syntax explanations, but in the example sections, you will learn clearly.

Popular Course in this category
Sale
Power BI Training Certification
4.5 (521 ratings)
6 Online Courses | 29+ Hours | Verifiable Certificate of Completion | Lifetime Access
View Course

Power-BI-GroupBy

How to Use GroupBy Dax Function in Power BI?

To use the Power BI groupby function, we need at least two tables, and below are the two tables I am going to use it for this DAX function demonstration.

Excel Data

Between the first table (Sales Table) and the second table (Buyers Table), the common column is “Category.” In the “Buyers” table, we have what is the category for each buyer, so using these two tables, we will group the data.

While grouping the data, we need to identify what are the columns we need in the new group table; in this case, I need “Category, Buyer, Sale Value, and Cost Value.”

Copy and paste the data directly to Power BI, or you can copy the data to an excel file and then import it to Power BI as an Excel file reference. So you can download the excel workbook template from the below link, which is used for this example.

You can download this Power BI GroupBy Excel Template here – Power BI GroupBy Excel Template

I have directly uploaded the data to Power BI.

Imported Data

  • Since we are grouping the data, we need to store it as a new table, so in Power BI, go to “Modelling” and click on “New Table.”

Power BI Group By - Enter New Table

  • This will create a new table, and in the formula bar, we can see it has highlighted the same.

New Table

  • First, give a name to this new table as “Group by Category & Buyer.”

Change Table Name

  • After the table name, put equal sing and open Power BI GROUPBY DAX function.

Insert Group By Function

  • The first argument of power bi GROUPBY DAX function is Table i.e., out of available two tables from which table we need to group the data, in this example, we need to group the data from the “Sales” table, so supply the table name as “Sales.”

Power BI Group By - Agrument 1

  • The next argument is GroupBy_ColumnName1 i.e., based on which column we need to group the data. So from “Sales Table,” we need to group by “Category-wise,” so select the “Category” column from “Sales Table.”

Power BI Group By - Agrument 2

  • Next, from the “Buyers” table, we need to mention the column name to be grouped. So from the “Buyers” table, we need to group by “Buyers-wise.”

Power BI Group By - Column To be Grouped

  • Ok, specifying column names is over. Next we need to give a name to the aggregate column. Since we are aggregating the “Sale Value” column, we will give the same name only.

Power BI Group By - Aggregate Column Name

  • Next, for this new “Sales Value” column, we need to apply the DAX function to return the aggregate value. Open SUMX function in the Expression 1 argument.

Power BI Group By - SUMX Function

  • For this expression, we need to supply a special CURRENTGROUP() function.

Power BI Group By - Currentgroup Function

Note: CURRENTGROUP() function can be used inside the Power BI GroupBy function only. This function access to the sub-table representing the current group in GROUPBY function.

  • In the next argument of SUMX function, open SUM function.

Power BI Group By - SUM Function

  • Since we are adding the “Sales Value” column from the “Sales” table, enter the column name. After this, close two brackets for SUMX & SUM functions and enter a comma.

Power BI Group By - SUM (Table Name)

  • Name 2 is the argument of GROUPBY function i.e., what the name of the second aggregate column is, enter the name as “Cost Value” in double-quotes.

Power BI Group By - 2nd Aggregate Column Name

  • For this also open SUMX function, inside this open CURRENTGROUP function and for Expression open SUM function and choose the “Cost Value” column from the “Sales” table.

Power BI Group By

  • Now close three brackets for Power BI SUMX, SUM, and GROUPBY functions and hit the enter key to close the DAX formula. Then you can see three tables here. Select the newly created table for grouping. In this; we can see DAX expression in the formula section.

Group By - Result

As you can see above, we can see the aggregated grouped table.

Note: I have done so much of formatting to this table, you can download the Power BI RANKX Template from the below link and apply each formatting technique as applied.

You can download this Power BI GroupBy Template here – Power BI GroupBy Template

Things to Remember

  • CURRENTGROUP is the new function that can be used inside the GROUPBY function only.
  • CURRENTGROUP takes the reference of the grouping table.
  • We cannot use the CALCULATE function in the Power BI GROUPBY DAX function.

Recommended Articles

This has been a guide to Power BI GroupBy. Here we discuss how to group together the data by collecting the information from two different tables using Power BI GROUPBY DAX function. You can learn more from the following articles –

  • Create Power BI Parameters
  • Power BI KPI
  • Reports in Power BI – Examples
  • Power BI Waterfall Chart
  • Power BI Themes
6 Shares
Share
Tweet
Share
Power BI Training Certification
  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Download Power BI GroupBy Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Power BI GroupBy Excel Template

Special Offer - Power BI Training Certification (22+ Hours of Training) View More