Power BI Group By Guide for Effective Data Insights

The Group By feature in Power BI is used to summarize data based on one or multiple aggregate functions.

With the Group By feature, you can find the average, min, max, count, and other aggregate values for one column. And that will be based on the unique values in other columns.

In this guide, you’ll learn what the Power BI Group by is, why it functions in Power BI, and an example of the group by in Power BI DAX.

Table of Contents:

1. What is Power BI Group By?
2. Why Group By Function in Power BI?
3. How to Use Power BI Group By Measure?
4. How to Visualize Numbers in Power BI?
5. Wrap Up

First…

What is Power BI Group By?

The “Group By’ feature in Power BI is used to aggregate data by grouping similar values together. Users are allowed to summarize and analyze data by creating groups based on one or multiple columns.

For instance, sales data can be grouped by region to see total sales per region. The “Group By’ feature helps in creating summarized reports, simplifying complex datasets, and calculating aggregates like sums and averages.

Why Group By Function in Power BI?

• Data Summarization: Grouping allows you to aggregate data by specific categories. It also allows you to simplify large datasets into more manageable and insightful summaries. For instance, there’s the option of grouping sales data by product category to see total sales for each category. All these make it easy to identify trends and patterns.
• Enhanced Analysis: Grouping data helps you perform aggregate calculations like sums, averages, and counts. It enables the user to analyze and compare different data groups and focus on key metrics. This will, in turn, help you analyze data more effectively.
• Improved Clarity: Grouping data helps reduce clutter, thereby showcasing a clearer picture of the data.
• Customized Reports: It helps users create customized reports that focus on specific aspects of the data. For instance, you can group sales data by year to track performance over time. You can also group sales data by department to evaluate departmental contributions.
• Facilitation of Insights: Grouped data can be visualized in multiple ways like bar charts, tables, and pie charts. This way, the user gains a different perspective about the data.

How to Use Power BI Group By Measure?

The Power BI Group By feature is found in the Power Query editor. There are two ways of loading the dataset into the Power Query editor.

• You’ll see the window below when importing data. Click on the “Transform Data” button to open the Power Query editor.

• If the data has been imported into the reports, you’ll view it by clicking the “Load” button. This can also open the Power Query editor. Next, click the “Transform data” option in the ribbon.

The window below displays when the Power Query editor opens. Click the “Group By” option in the ribbon.

The Group By window will open.

Using Group By On Multiple Columns

Group by can also be used to group data using multiple columns. To do that, you’ll have to select the “Advanced” option in the Group By window.

In this illustration, you’ll see how to group average fares by the Pclass (passenger class) and Sex columns. You’re allowed to select one column to use in the Group By feature by default. To add more columns, click the “Add grouping” button. All other options will remain as they were in the previous section.

You’ll see three columns in the output table as shown in the image below. The unique values in the Pclass column are repeated for each of the unique values in the Sex column. The average fare paid by the male passengers traveling in the first class was 67.22. On the flip side, the female passengers from the first class paid an average fare of 106.12.

Group By with Multiple Aggregation Functions.

To group data using multiple aggregate functions, you’ll have to click the “Add aggregation” button in the Group By window. This will add another row of input boxes to enable you to enter the relevant data.

The Group By feature in the image above uses two aggregate functions.

• The first aggregate function groups the average fare by the “Pclass” and “Sex” columns.
• The second aggregate function groups the maximum age by the “Pclass” and “Sex” columns.

The table summarizes the data. All the rows of data in the original table have generated valuable, actionable data. You can see the average fares paid by different genders in the various classes on the Titanic. You can also see the maximum age of the passengers in these groupings.

How to Visualize Numbers in Power BI?

Stage 1: Logging in to Power BI

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Application Channels	Initial Screening	Conduct Interviews	Employee Onboarding	Total Candidates
Social Media	Short Listed	Final Interview	Hired	32
Social Media	Short Listed	Final Interview	Not Hired	400
Social Media	Short Listed	Knocked Out		800
Social Media	Knocked Out			1100
Company Career Page	Short Listed	Final Interview	Hired	20
Company Career Page	Short Listed	Final Interview	Not Hired	250
Company Career Page	Short Listed	Knocked Out		500
Company Career Page	Knocked Out			900
Events	Short Listed	Final Interview	Hired	5
Events	Short Listed	Final Interview	Not Hired	100
Events	Short Listed	Knocked Out		200
Events	Knocked Out			350
Paper Media	Short Listed	Final Interview	Hired	3
Paper Media	Short Listed	Final Interview	Not Hired	80
Paper Media	Short Listed	Knocked Out		135
Paper Media	Knocked Out			700
Employee Referrals	Short Listed	Final Interview	Hired	10
Employee Referrals	Short Listed	Final Interview	Not Hired	70
Employee Referrals	Short Listed	Knocked Out		80
Employee Referrals	Knocked Out			110
Direct	Short Listed	Final Interview	Hired	25
Direct	Short Listed	Final Interview	Not Hired	150
Direct	Short Listed	Knocked Out		425
Direct	Knocked Out			600

• Paste the data table above into the “Power Query” window. After that, select the “Create a dataset only” option.

• Navigate to the left-side menu, and click on the “Data Hub” option. Power BI will populate the data set list. If no data set has been created, you’ll get an error message.

• Click on the “Create report” dropdown.

• To add the Power BI Sankey Diagram Extension by ChartExpo, you’ll have to use an add-in or Power BI visual from AppSource. To do that, Navigate to the right side of the Power BI dashboard, and open the Power BI Visualizations panel. Next, click the ellipsis symbol (…) to import the Power BI Sankey Diagram extension by ChartExpo. In the menu that opens, select the “Get more visuals” option.

• Enter “ChartExpo” in the highlighted search box. You’ll see the “Sankey Diagram for Power BI by ChartExpo” similar to the image below.

• Click on the Sankey Diagram. After that, click the highlighted “Add” button.

• Power BI adds the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.

• To draw a Sankey Diagram with ChartExpo’s Power BI extension, you’ll have to select the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel. You’ll see a window like the one below open in the report section of the Power BI dashboard.

• At this point, select the fields to use in the Sankey Chart.

• When selecting the fields, follow the sequence below:
• • Application Channels
  • Initial Screening
  • Conduct Interviews
  • Employee Onboarding
  • Total Candidates
• You’ll have to provide your email address or ChartExpo license key. Add the key under the visual section.

• After that, the Sankey Chart will be displayed on your screen. You can add the top header text in the chart under the “General” section.

• Click on Visual to set the number. You should also enable the options as shown below.

• You can change the “Node Font Style.”

• There’s the option to change the “Level Font Style.”

• You’ll see the Level Labels as shown below.

• The image below shows you how to change the Nodes color.

• Since you’ve seen “How to change Level 1 color,” follow the same steps to change the Nodes color of other levels.

• After changing the all-nodes color, here’s what the HR Dashboard in Power BI using ChartExpo will look like.

Insights

• Social Media is the primary application channel with the highest number of candidates (1100).
• Company Career Page follows with 900 candidates.
• Paper Media and Events have comparatively lower candidate counts of 700 and 350, respectively.
• Direct applications have 600 candidates.

FAQs

What is “Power BI Group By” in data analysis?

“Power BI Group By” in data analysis aggregates data into categories. This enables summary statistics like averages and sums. It simplifies large datasets and makes it easy to analyze and visualize key insights.

Can “Power BI Group By” be used for creating charts and visuals?

Yes, “Power BI Group By” can be used to create charts and visuals. It’s done by aggregating data into categories.

Is “Power BI Group By” useful for trend analysis?

Yes, “Power BI Group By’ is useful for trend analysis. It allows the aggregation of data by time periods or categories. This will, in turn, help highlight trends and patterns over time for clearer insights.

Wrap Up

Power BI Group By aggregate data into categories, and summarizes metrics like sums or averages. It simplifies analysis by organizing data, supports effective trend visualization and reporting, and enhances clarity.

The Power BI Group By feature is the best for data (or business) analysts who will be handling large datasets. It helps them consolidate information and interpret large datasets.

The creation of customized reports is another benefit of using the Group By feature. With the Group By feature, you can focus on specific aspects of your data. It also helps present a clearer picture of your data. This will, in turn, reduce clutter, and highlight significant trends and comparisons.

Now you know what the Power BI Group By feature is all about, what kind of data will you be analyzing with it?