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.
***** Related Links *****
Understanding How The AVERAGEX Function Works
Measure Averages Per Day Using AVERAGEX With DAX
Using Iterating Functions SUMX And AVERAGEX In Power BI
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
