Power BI TOPN Function: Comprehensive Guide with 2 Examples

Power BI is a very popular Business Intelligence tool currently available in the market. This tool has millions of users worldwide and is very flexible and efficient. It provides many features and allows to create detailed and interactive reports and dashboards for all kinds of data. Its main highlight is the availability of functions called as DAX to create expressions that returns different values based on the requirements. In This article we give a comprehensive guide to Power BI TopN function in detail.

Table of Contents

What is Power BI?

Image Source: www.pei.com

Power BI is a leading Saas(software as a service) platform that was launched by Microsoft Corporation. It is a BI (business intelligence) tool that is used to create interactive and detailed reports, interactive dashboards and perform real-time analysis on organizational data. Power BI is available on various platforms such as desktop applications, mobile applications, and even web applications as well. This makes it highly flexible and allows you to collaborate with the organization from anywhere around the world.

PowerBI is used to create detailed reports and it provides tools like Reports Builder. Different departments of the organization can collectively use Power BI. for example sales and marketing teams can use it to find the campaign trends, conversions, and many more.

Learn more about Power BI.

Key Features of Power BI

Power BI is a popular tool that is used to create reports and dashboards. Power BI is a feature-rich tool. Some of the key features are mentioned below.

1. Detailed and interactive dashboards and reports

Power BI is used to create detailed reports, with multiple designs that can e used based on requirements. Some of its visualizations are given below:

Bar Charts
Column Charts
Line Plots
Area Plots
Pie Charts
Scatter Plots
Treemaps
Funnel Analysis Charts, etc

2. Informative navigation pane

the Power BI navigation pane helps you collaborate with colleagues and other collaborators. It’s dynamic and you can easily navigate to dashboards or reports created for both personal use (My Workspace) and collaboration. Also, save all dashboards and reports related to a particular department in your organization in the Workspace column. It also helps you navigate to related apps, deployed pipelines, recent work, starred dashboards, and reports.

3. Multiple Functions for analysis

Power BI provides DAX functions which are inbuilt functions that analyze the data of the organization. These functions are predefined and present in the function library of Power BI. These functions are updated on regular basis.

4. Multiple Dataset Visualizations

Power BI is highly flexible and scalable. It allows the data from single or multiple sources. Data can be on-premise or present in the cloud. It also supports structured and unstructured data. A few major data sources that are supported by Power BI are python, oracle, excel, and many more.

5. Customizable Dashboards

You can create Dashboards using the pre-built templates or you can custom create them according to your requirement. These Dashboards can also be shared with others.

What are DAX Functions?

power bi topn: dax — Image Source: yodalearning.com

DAX function stands for Data Analysis Expression. It is a collection of functions that can be used to create expressions and formulae in Microsoft SQL Server, Power BI desktop application, and many more. Every DAX function applies a different operation, on the values present in an argument or an expression.

Data Analysis Expressions (DAX) is a collection of functions and operators that can be used to create formulae and expressions in Microsoft SQL Server Analysis Services, Excel Power Pivot, and Power BI Desktop. Every DAX function applies a specific operation to the values in an argument. In a DAX formula, you can specify several arguments. New functions and functionality are added to DAX regularly to accommodate new features. Service, application, and tool upgrades, which are usually done periodically, contain new features and changes.

Some of the Power BI DAX functions are:

Time Intelligence Functions: These functions help you create calculations that use the built-in calendar and date knowledge. You can combine time and date range with aggregates or other calculations to make meaningful sales, inventory, and other comparisons over similar periods.
Date and Time Functions: These are used to perform date and time value calculations.
Logical Functions: These are used to logically evaluate an expression or argument and return TRUE or FALSE depending on whether the condition is met.
Mathematical and trigonometric functions: These are used to perform various math operations on a given value.

There are many more DAX functions supported by Power BI. Refer to DAX Function Reference documentation to learn more about other DAX functions.

What is Power BI TOPN function?

Power BI TopN function is a DAX inbuilt function of the Power BI function library. This function is used to return the first n records of the data. The function matches the parameters and returns the rows of the database.

Syntax:

TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])

Parameters:

parameter	description
n_value	This parameter defines the number of rows to be returned. when the expression is evaluated multiple times(for each context/row), the DAX expression returns a scalar value. Remarks can be read for better understanding when the number of rows returned is greater than n_value.
table	This parameter is defined where the DAX expression is required to return a table of data and extract top ‘n’ rows. Remarks can e read for better understanding when an empty table is returned.
orderBy_expression	This parameter is to be used where the result of DAX expression is used to sort the table and can be evaluated for each row.
order	(Optional) A value that specifies how to sort orderBy_expression values: – 0 (zero) or FALSE. Sorts in descending order of values of order_by. Default when order parameter is omitted. – 1 or TRUE. Ranks in ascending order of order_by.

Return value:

The Power BI TopN function returns top ‘n’ rows of a table. It can also return an empty table if n_value is equal to ZERO. Rows may not be sorted in any order.

Remarks

If there is a conflict where many rows match the context, in order_by values, all the matched rows are returned. This means that when there is a tie, the number of rows returned may be higher than the n_value that is specified.
if the specified value for n_value is less than or equal to zero then Power BI TopN returns empty table.
Power BI TopN doesn’t return values in any sorted order.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Examples:

Power BI TOPN function examples

Power BI topn function is most effective when used with other functions.

Power BI TopN: Example 1:

= SUMX(
    TOPN(10, 
        SUMMARIZE(Product, [ProductKey], "TotalSales", 
            SUMX(RELATED(InternetSales_USD[SalesAmount_USD]), 
            InternetSales_USD[SalesAmount_USD]) + SUMX(RELATED(ResellerSales_USD[SalesAmount_USD]), 
            ResellerSales_USD[SalesAmount_USD])
            )
        )
    )

In this example Power BI TopN is used along with sumx DAX function. This example returns top 10 rows of table.

Power BI TopN: Example 2:

TOPN (
        3,
        ADDCOLUMNS (
            VALUES ( 'Product'[Product Name] ),
            "@Sales Amount", [Sales Amount]
        ),
        [@Sales Amount],
        DESC
    )
ORDER BY [@Sales Amount] DESC

In this example Power BI TopN is used along with addcolumns DAX function. This example returns top 3 rows of table.

Conclusion

Power BI is a leading and popular BI tools from the house of Microsoft and used by millions. It provides many features and DAX functions is one such feature where the functional are predefined and returns values based on expressions. In this article we talked about one such DAX function, Power BI TopN function along with its examples.

Optimize your data analysis in Power BI by leveraging the RANKX function for effective ranking and comparison. Explore the details here: RANKX Power BI.

PowerBI is a trusted source that a lot of companies use as it provides many benefits but transferring data from it into a data warehouse is a hectic task. The Automated data pipeline helps in solving this issue and this is where Hevo comes into the picture. Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built Integrations that you can choose from.

visit our website to explore hevo

Hevo can help you Integrate your data from numerous sources and load them into a destination to Analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure.

Share your experience of learning about Power BI TopN function in the comments section below.

Arsalan Mohammed Research Analyst, Hevo Data

Arsalan is a research analyst at Hevo and a data science enthusiast with over two years of experience in the field. He completed his B.tech in computer science with a specialization in Artificial Intelligence and finds joy in sharing the knowledge acquired with data practitioners. His interest in data analysis and architecture drives him to write nearly a hundred articles on various topics related to the data industry.