Counting per group is one of the fundamental data analysis techniques. It involves determining the number of occurrences of a particular value or combination of values within a dataset. This technique is widely used in various fields, including business intelligence, marketing, and social sciences.

Power BI offers several methods for performing group counting effectively. In this article, you will learn the methods and tools in Power BI that will help you to efficiently count the number of values in your grouped data.

Count Per Group with Visualizations

Visuals in Power BI do one of three things – filter, group, and summarize values. Because of these properties, they can return the count of grouped values. Another reason for this is possible is measures.

In Power BI, measures are DAX (Data Analysis Expressions) formulas that perform calculations on data. They are used to summarize and analyze data in Power BI reports and visualizations.

Measures can be used to calculate aggregations, such as counts, sums, averages, and percentages. They can also be used to create more complex calculations, such as ratios, rankings, and running totals.

Measures can be either explicit or implicit. Explicit measures are defined using DAX expressions. Implicit measures are numeric columns that will be summarized when added to a visualization’s field well that supports summarizing.

In Power BI, implicit measure columns will carry the sigma sign (Σ) in front of them in the Model view. This indicates that they can be aggregated using the default summarization technique available.

Power BI provides the following aggregation functions for summarizing numeric columns.

• Sum
• Average
• Minimum
• Maximum
• Count (Distinct)
• Count
• Standard deviation
• Variance
• Median

Let’s say you want to see the number of sales orders received by each business’s outlets using a table visual. The first thing you will do is to add the columns to the visual.

Then, in the Columns well, you will click on the column you want to summarize and select Count from the dropdown options.

The visual will now show the number of orders in each country for the given period.

Note: Aggregation is also possible on text, date, and Boolean columns even though they don’t carry the sigma symbol. Although the aggregation techniques available differ.

Count Per Group with DAX

Leveraging DAX, you can create explicit measures that will return the count of grouped data using either the COUNT or COUNTX function. Both functions simply do the same thing, they return the number of values in a specified column or table.

Their syntax is as follows.

COUNT ( <ColumnName> )

To use the COUNT function, you only need to specify the column with the values you want to COUNT.

COUNTX  ( <Table>, <Expression> )

For the COUNTX, you need to specify the table containing the values you want to count, then define an expression that will evaluate for every row in the table.

When you use both functions, they will return the count of values in the column you’ve specified. From there, you can then use the filter context property of measures to return the count per group when you add it to a visual.

COUNTROWS DAX Function

COUNTROWS is another function you can use to return the count of the number of occurrences of values. Unlike the COUNT functions, it only takes a table argument because it counts the number of rows in a table, although this is an optional argument and isn’t needed for the function to return a value.

COUNTROWS ( [ <Table> ] )

While the COUNTROWS function might seem like a straightforward approach for counting items in a column, it’s not the most optimal choice for several reasons. Firstly, it considers the entire table instead of just the specific column, leading to less precise results.

Additionally, it counts rows regardless of blank cells, potentially inflating the actual number of items. For COUNTROWS to deliver more targeted results, it requires combining it with a CALCULATE or FILTER function.

CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )

The CALCULATE function evaluates an expression within a modified filter context. In other words, it returns aggregations based on the specific filters where they’re calculated or evaluated. CALCULATE is a very powerful DAX function that’s useful in many scenarios.

COUNTROWS = 
CALCULATE (
    COUNTROWS(
        'Sales Order'
    ),
    NOT ISBLANK(
        'Sales Order'[Sales Order]
    )
)

To use the CALCULATE function to ensure the COUNTROWS function returns reliable results, you can create a syntax that follows the format above. Essentially, the syntax returns the count of non-blank rows in the specific column.

It produces similar results with the rest when added to a visual and this way, you can know the results are reliable.

FILTER ( <Table> , <Filter> )

The FILTER function is another you can pair with COUNTROWS to return the number of values in a column. FILTER returns a subset of another table. It’s two arguments <Table> points to the table that’s going to be filtered, and <Filter> is a Boolean filter expression that evaluates for each row the <Table>.

COUNTROWS (FILTER) = 
COUNTROWS(
    FILTER(
        'Sales Order'
        ,
        NOT ISBLANK(
            'Sales Order'[Sales Order]
            
        )
    )
)

You can use this formula to return the number of non-blank values in a column. Here’s a breakdown of the formula:

• COUNTROWS(FILTER(…)) – This outer function counts the number of rows returned by the inner function FILTER(…).
• FILTER(‘Sales Order’, …) – This inner function filters the ‘Sales Order’ table based on the condition specified within the function.
• NOT ISBLANK(‘Sales Order'[Sales Order]) – This condition checks if the ‘Sales Order'[Sales Order] column is not blank. In other words, it filters out any rows where this column contains blank values.

The syntax returns the same values as the other measure when added to a visual.

GROUPBY, SUMMARIZECOLUMNS, and SUMMARIZE

The GROUPBY, SUMMARIZECOLUMNS, and SUMMARIZE functions are a powerful set of tools for grouping data based on specific columns. By doing so, you can effectively break down and analyze trends within these groups.

Each of these functions empowers you to derive new columns by applying calculations to the grouped data. With this capability, you can incorporate valuable insights such as totals, averages, or counts into your analysis.

GROUPBY

The GROUPBY function generates a summary of the input table by organizing data based on specified columns. It returns a table that includes the chosen columns specified by the groupBy_columnName arguments and additional columns grouped according to the name arguments.

Its syntax is as follows:

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

table – any DAX expression that returns a table of data.

groupBy_columnName – specifies the name of a column, either in the table or a related table, to group the data by. This parameter cannot be an expression.

name – the name assigned to the newly added column in the GROUPBY column list, enclosed within double quotes.

expression – one of the aggregation functions, taking CURRENTGROUP() as its first argument.

GROUPBY is mainly used to aggregate results from a computed column in a preceding table expression. It effectively combines or summarizes the outcomes of a preceding table operation. If you need to aggregate or group results based on specific criteria, GROUPBY is your go-to solution.

The GROUPBY uses the CURRENTGROUP within aggregation functions in the expression argument to generate values for the additional extension columns. The CURRENTGROUP function ensures the expression is evaluated for every row in the table output of the GROUPBY.

SUMMARIZE

The SUMMARIZE function creates a summary of the input table grouped based on specified columns. Although it shares a similar purpose with the GROUPBY function, they differ in various aspects.

The syntax for the SUMMARIZE function is as follows:

SUMMARIZE ( <Table> [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] )

table – the base table with a “to-many” relationships to other tables in the model.

GroupBy_ColumnName – the column to group by.

Name – the name of the new column to be added to the table.

Expression – the expression used to generate values for the new column.

Caution: Avoid using Name and Expression arguments within SUMMARIZE due to their complexity and potential for unreliable results. SUMMARIZE’s clustering process, while enabling filtering with local columns, can impact performance and accuracy.

While SUMMARIZE and GROUPBY share similarities in grouping data, they excel in different domains. SUMMARIZE shines when grouping by model columns, while GROUPBY excels in aggregating columns in local DAX tables.

The clustering capability of SUMMARIZE allows for filtering model columns using local columns, which is a strength that GROUPBY lacks due to the absence of model lineage in local columns.

However, by strategically combining both functions within ADDCOLUMNS queries, optimal results can be achieved.

SUMMARIZECOLUMNS

SUMARIZECOLUMNS was introduced to resolve the optimization shortfalls that existed in the SUMMARIZE function. It also provides additional features that didn’t exist in SUMMARIZE.

The syntax for the SUMARIZECOLUMNS function is as follows:

SUMMARIZECOLUMNS ( [<GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] ] ] )

GroupBy_ColumnName – the column to group by.

FilterTable – an expression that defines the table from which rows are to be returned.
Name – a column name to be added.

Expression – the expression of the new column.

SUMMARIZECOLUMNS returns a table that is a combination of values from the specified columns, based on the specified grouping.

With these functions, you have the flexibility to get the count of your dataset across various dimensions.

Assuming you want to calculate the number of items sold in each country for instance, you can use a combination of the GROUPBY and SUMMARIZECOLUMNS functions to achieve the outcome. Here’s how.

DEFINE
    VAR CountrySales =
        SUMMARIZECOLUMNS (
            DimSalesTerritory[SalesTerritoryCountry],
            DimProduct[EnglishProductName],
            "Order Quantity",
            SUM(FactInternetSales[OrderQuantity])
        )

EVALUATE
GROUPBY (
    CountrySales,
    [SalesTerritoryCountry],
    "No. of Products Sold per Country",
        COUNTX (
            CURRENTGROUP (),
            [EnglishProductName]
        )
)

This code defines a measure that analyzes sales data based on countries and products sold.

The CountrySales variable creates a table with columns for country, product name, and total order quantity for each combination using the SUMMARIZECOLUMNS function.

 The parameters in the SUMMARIZECOLUMNS syntax are explained below.

        SUMMARIZECOLUMNS (
            DimSalesTerritory[SalesTerritoryCountry],
            DimProduct[EnglishProductName],
            "Order Quantity",
            SUM(FactInternetSales[OrderQuantity])
        )

• DimSalesTerritory[SalesTerritoryCountry]: specifies the country for each sales territory.
• DimProduct[EnglishProductName]: specifies the product name for each product.
• “Order Quantity”: the name of the new column in the summarized table.
• SUM(FactInternetSales[OrderQuantity]): This expression calculates the total order quantity for each combination of countries and products.

When this syntax is evaluated alone, it returns a table with three columns – SalesTerritroyCountry (the groupby dimension), EnglishProductName (the filter dimension), and Order Quantity (the expression).

EVALUATE
ADDCOLUMNS(
    SUMMARIZE(
        FactInternetSales,
        DimSalesTerritory[SalesTerritoryCountry],
        DimProduct[EnglishProductName] ),
        "Order Quantity",
        [COUNt]
        )       

This is an alternative syntax that returns the same results. Here, the ADDCOLUMNS function is paired with the SUMMARIZE function. ADDCOLUMNS basically extends a table by adding new columns using DAX expressions. It has a simple syntax.

ADDCOLUMNS ( <Table>, <Name>, <Expression> [, <Name>, <Expression> [, … ] ] )

Table – the table to which new columns are added.

Name – the name of the new column to be added.

Expression – the expression for the new column to be added.

In this syntax, the SUMMARIZE creates the table that’s passed into the table argument in ADDCOLUMNS which is then extended by the Order Quantity column.

This table presents a breakdown of the quantity of each specific product ordered in different countries.

The GROUPBY part of the syntax then returns the total number of products sold in each country using the syntax:

GROUPBY (
    CountrySales,
    [SalesTerritoryCountry],
    "No. of Products Sold per Country",
        COUNTX (
            CURRENTGROUP (),
            [EnglishProductName]
        )
)

• CountrySales: the name of the variable that stores the table generated using the SUMMARIZECOLUMNS function. It’s used here as the input for GROUPBY.
• [SalesTerritoryCountry]: the column to group the data by (country).
• “No. of Products Sold per Country”: the name of the new column containing the aggregation result.
• COUNTX: counts the number of rows in a table that meet a specific condition within each group.
• CURRENTGROUP(): refers to the current group defined by the GROUPBY clause (country in this case).
• [EnglishProductName]: this specifies the condition for counting within each group – it counts the number of distinct product names.

Therefore, this code uses GROUPBY to count the number of unique products sold in each country based on the CountrySales table created earlier.

Conclusions

In conclusion, calculating the count per group unlocks a powerful tool for analyzing and understanding your data within Power BI.

By leveraging measures through the use of DAX functions and visualizations, you can easily glean valuable insights into trends, patterns, and relationships that are present within your datasets.

You can then use this information to inform data-driven decisions and support strategic planning, ultimately propelling your business or organization towards success.