Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Using AVERAGEX In Power BI – A DAX Tutorial & Examples

by | 1:00 pm EDT | July 23, 2020 | Power BI

AVERAGEX in Power BI is an incredibly versatile function. It’s not just for averaging values; it’s also great for trend analysis. You may watch the full video of this tutorial at the bottom of this blog.

I discussed the use of AVERAGEX for trend analysis in the Enterprise DNA Learning Summit. A link to the event can be found below.

But in this post, I’m going to focus more on what happens in the background when the AVERAGEX function is used. Understanding this function is key in figuring out what scenarios would require its use.

## Using AVERAGEX For Average Sales Per Day

I’m going to start with one of the simplest ways to use AVERAGEX and find out what my average sales per day is.

It all boils down to knowing what the initial context of the calculation is, then knowing what virtual table to reference.

The initial context in this case would come from the Product Name.

Since AVERAGEX works as an iterating function, it’s going to add logic to each row in the table.

But before AVERAGEX can add any logic to the columns, it needs a virtual table to reference data from. This is what the VALUES function does.

Since I’m looking for the Average Sales Per Day, I will reference the Date table.

This means that I’m looking at every product for each day and finding out which part of the daily Total Sales comes from each product.

Once all that is done, the Total Sales for every product is averaged. These are the results in my Avg Sales Per Day column.

Remember that since the function looks at the average, the calculations would still include the days with zero sales.

## Using AVERAGEX For Average Sales Per Customer

This time, I’m going to use AVERAGEX to find out my average sales per customer.

The measure is similar to the one I used for Avg Sales Per Day. The only difference is that I’m referencing the column for Customer Names instead of the Date.

Let me add that to the table here so I can see what results will come out for every row corresponding to each product.

Previously, AVERAGEX was iterating through every single day for every product. This time, it’s iterating through every customer who bought each specific product.

So the iterating function is now evaluating the Total Sales for each product bought by each customer. In the end, it finds its average.

## Using AVERAGEX For Average Sales Per Month

The previous two examples showed how simple it is to use AVERAGEX in Power BI. But it can also be used for more complex applications, especially in terms of the virtual table being referenced in the measure.

I’m going to calculate the Average Sales Per Month to show a different way of referencing a virtual table through the VALUES function.

I can’t just reference the Date table here directly because I’ll end up getting the same results as my Avg Sales Per Day column.

This is one benefit of using the VALUES function. In the previous examples, some would skip using VALUES and would just reference the required table directly. But when VALUES is used, more calculations occur in the background.

So I’m still referencing the Date table, but I’m not looking at the average at the end of each day. Instead, I look at the month and year and find the average in the end.

## Conclusion

AVERAGEX is a function that gives powerful insights when used correctly. The way I presented this iterating function in this tutorial is just an overview of what it can do.

Again, you can get a more in-depth look at how to use AVERAGEX in Power BI in the Enterprise DNA Learning Summit.

You’ll see that even in complex scenarios, the AVERAGEX function can give you more insights that can be useful in your dashboards and reports.

All the best,

Sam

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## How to Interpolate in Excel: User Guide With Examples

In data analysis, interpolation plays a crucial role in estimating values that fall between known data...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...