Power BI Calculate Group by Measure

Are you looking at calculating group by measure in Power BI? If your requirement is yes, with the help of the Power BI group by function, you can achieve that.

In this Power BI tutorial, we will learn what a Power BI group by measure is and how to work with Power BI to calculate group by measure. Also, I will tell you the Power BI sum group by function measure.

Power BI GROUPBY function

  • In Power BI, group by measure allows you to summarize your data by a specific category or attribute, like sales by product category or total revenue by region.
  • It’s like bundling similar items and then calculating something about each bundle, such as the average, sum, or count. The GROUP BY function returns the table.

Power BI GROUPBY function syntax:

GROUPBY (<table> [, <groupBy_columnName> [, <groupBy_columnName> [, …]]] [, <name>, <expression> [, <name>, <expression> [, …]]])

Where:

  • <table> = Name of the table where we group the data.
  • <groupBy_columnName> = Name of the column in the table by which the data will be grouped.
  • <name> = Name of the new column.
  • <expression> = DAX function, we need to aggregate the values.

Power BI Calculate Group by Measure

I will tell you how to calculate group by measure in Power BI.

Here, I have a SharePoint list (Orders) that contains below columns with various data types:

ColumnsData Types
Order DateDate and time
Product NameSingle line of text
CategorySingle line of text
Sub-CategorySingle line of text
SegmentSingle line of text
CitySingle line of text
SalesCurrency
ProfitCurrency
group by dax measure power bi

1. Open Power BI Desktop and load the data. Then, you see data in the Data pane.

group by power bi dax measure

2. Go to the Table view and click on New table.

group by measure in power bi

3. In the formula bar, click the below expression. Then click Commit.

Group by Category = 
GROUPBY(
		'Orders',
		'Orders'[Category],
		'Orders'[Sub-Category],
		"Count of Product",
		COUNTX(
			CURRENTGROUP(),
			COUNT('Orders'[Product Name])
		)
	)

Where:

  • Group by Category = Name of the Measure
  • Orders = Name of the Table
  • Category = Name of the Column
  • Sub-Category = Name of the Column
  • Product Name = Name of the Column
  • COUNTX = DAX Function
  • CURRENTGROUP = DAX Function
  • COUNT = DAX Function
group by in power bi measure

4. After clicking the commit button, you see the table in the Table view.

power bi count group by measure

This way, you can use group by measure in Power BI.

Power BI SUM Group by function Measure

Now, I will tell you how to calculate sales by product category using Powe BI group by function measure.

If you have a requirement like this, follow the below steps:

1. Goto the Table view. Under the Home tab, click the New table.

power bi sum group by measure

2. In the formula bar, put the expression below. Then click the Commit button.

Sales by Product Category = 
GROUPBY(
		'Orders',
		'Orders'[Category],
		"Sales",
		SUMX(
			CURRENTGROUP(),
			'Orders'[Sales]
		)
	)

Where:

  • Sales by Product Category = Name of the Measure
  • Orders = Name of the Table
  • Category = Name of the Column
  • Sales = Name of the Column
  • SUMX = DAX Function
  • CURRENTGROUP = DAX Function
power bi new measure sum group by

3. After clicking the commit button, you see the table in the Table view.

power bi sumx group by measure

Also, you can compare whether these sales values are right or wrong by using the below steps:

1. Goto the Report view. Under the Home tab, expand the Visual gallery(black box). Then click the Table visual.

power bi dax measure sum group by

2. Then, using the +Add data option, add Category and Sales into the Columns field from the Orders table. Check the screenshot below.

Power BI GroupBy measure

3. After that, you compare two tables. Check the screenshot below.

SUMX Group by Measure in Power BI

This way, you can calculate sales by product category using the Powe BI group by function measure.

Conclusion

I hope now you can calculate any summarized data using the Power BI group by measure.

In this tutorial, we learned what a Power BI group by measure is and how to calculate a group by measure in Power BI. Additionally, we explained the Power BI sum group by function measure.

Moreover, you may like some more Power BI articles: