Use aggregate functions on rows in Power BI

Use aggregate functions on rows in Power BI

Using aggregate functions on columns is fine, but doing the same on rows is — pure beauty! And all this with a single line of DAX code!

Aggregate functions are one of the main building blocks in Power BI. Being used explicitly in measures, or implicitly defined by Power BI, there is no single Power BI report which doesn’t use some sort of aggregate functions.

What are the aggregate functions?

Aggregating means combining values in your data performing some mathematical operation. That can be SUM, AVERAGE, MAXIMUM, MINIMUM, COUNT, COUNT DISTINCT, MEAN, STANDARD DEVIATION, etc.

However, in-depth observation of aggregate functions is not in the scope of this article. Here, I wanted to demonstrate how you can use aggregate functions in an unconventional way, since I believe it can be useful in some specific scenarios.

Default behavior of aggregate functions

By default, aggregations are being calculated on columns. Let’s take a look at following basic example:

Image for post

This is a typical example of SUM aggregate function. Numbers are being aggregated on Year and Month level, and finally, in the end, we can see the total of individual values in the table.

We could also perform AVERAGE to find average values, MIN or MAX to find the minimum and maximum values, etc. Pretty straightforward and probably already known for most of the people who ever worked with Power BI or Excel.

data-science data data-modeling power-bi towards-data-science data analysis

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

How to reduce your Power BI model size by 90%!

The final part of “Brain & Muscles” behind Power BI series, shows a real-life showcase of data model optimization and emphasizes general rules for reducing data model

Power Query tips for every Power BI Developer

Use these simple yet powerful techniques to speed-up your Power BI development. If someone asks you to define the Power Query, what should you say?

Explore your JIRA Data with Power BI

JIRA Software provides bug tracking, issue tracking, and project management capabilities for teams and organizations. The JIRA content pack for Power BI helps you quickly import JIRA data so you can get an instant dashboard to analyze workloads...

Managing your Data with Microsoft’s Power BI

In a world where the sheer amount of data is often overwhelming, the ability to interrogate and organise data to make meaningful business decisions is more important than ever. Microsoft have created Power BI to enable every day users to use the...

Power BI 101 — Data Shaping in a nutshell

In the 2nd part of the Power BI 101 series, check what is Data Shaping and why learning this concept can bring your Power BI data model to new heights