# DAX Calculations: Total Of Average Results

This is a very good demonstration of DAX calculations in Power BI. I will show you how you can calculate the total of a range of average results, utilizing the correct DAX formula combination. You may watch the full video of this tutorial at the bottom of this blog.

This requires a reasonable understanding of how iterating functions work within Power BI. These are functions like SUMX, AVERAGEX, MINX, MAXX. Usually the function has an X on the end.

Once you can understand what iterating functions do, you can have a much better understanding of how to manipulate a formula to be able to calculate a unique total.

With this example, we work through AVERAGEX to get the average. Then, I’ll show you how to use SUMX as part of the formula to then re-calculate up a different total than what you would ordinarily receive if you just utilize AVERAGEX.

We also cover the HASONEVALUE function, which is quite widely used in very similar scenarios.

## Looking Into The DAX Calculations

Let’s look at this table and evaluate what is actually occurring in these formulas inside the table.

First of all, we take a look at Total Sales. It is a simple sum formula or SUMX formula, which is an iterating function.

Itâ€™s just doing a sum, but we are evaluating a logic at every single row because thatâ€™s what iterating functions do, specifically SUMX.

The key point here is when it gets to the total, which is 35 million. Because there was no context on this result, it’s actually iterating at every single row in the underlying Sales Table, completing this logic. And thatâ€™s how we get the 35 million.

But then, when we jump to AVERAGEX, it’s the exact the same formula. It has the same parameters inside the formula, except that we have the average here.

Now letâ€™s look at the total, 2,356.01. The total is going to be what is calculated at every single row, just like it is in SUMX. But instead of doing a sum, itâ€™s doing an average.

Thatâ€™s why we have a low number here, which is 2,356.01. It is the average of every single sale that has ever happened in the Sales Table.

Thereâ€™s an evaluation happening in every single row and itâ€™s saving it into the memory, and then going to the next row and the next.

Right at the end, itâ€™s going to average up every single sale transaction that has occurred.

But the key part of this tutorial is to know how we got this total, 28,272.12. We want to find out how to workout a total of all of our averages.

## Calculating Total Of Averages

To get the total of average results, we need to create some different behavior in the total area.

How do we do it? First, letâ€™s review the formula that I have created.

If we check out the variable, AVERAGEX, weâ€™ll recognize that this is exactly the same as what is occurring in this particular part of the table.

We use this logic, IF HASONEVALUE, where I go Dates Month because we are in a monthly context.

If the monthly context has one value, then we are going to evaluate the same exact formula (AvgSales), which you see here itâ€™s exactly the same.

But if itâ€™s not, which means itâ€™s basically in the total area, then we want to do something different.

We will do a SUMX because we want to create somehow a sum of the Average Sales. And we want to evaluate in the total (28,272.12) every single month and sum up the Average Sales.

So at the total (28,272.12), instead of evaluating every row of this Sales Table, we are evaluating every month.

We get the Average Sales of each month, and instead of doing an average, we are doing a sum because inside is SUMX.

And thatâ€™s actually how we get the sum total of all the averages.

## Implications Of The Total Of Average DAX Calculations

There are many ways you can use this technique of combining formulas to create this average totals. One example is when you want to get the Average Sales per Financial Year, as I did in this example.

Youâ€™ll see in the example that the total of averages is in every single row.

I calculated that using the ALLEXCEPT, removing all the context in the financial year and thatâ€™s how the total pop up to all of these.

Then, I worked out the Percentage Per Financial Year (% per FY). It’s done by dividing Average Sales by Average Sales per FY.

Youâ€™ll know that itâ€™s actually working because this is a hundred percent here, as it should be.

## Conclusion

This topic on DAX calculations for total of average has been widely requested on the forum and elsewhere.

When getting the total of averages, we use a combination functions like AVERAGEX, HASONEVALUE, and SUMX with some logic combined to get the ultimate total of all averages.

This is a very reusable idea if you need to get totals that are quite different to what is actually the general calculation in your tables or your visualizations.

Here are other related topics involving SUMX:
Show Last N Sales Of A Customer Only Using Power BI
How To Understand Virtual Tables Inside Iterating Functions In Power BI â€“ DAX Concepts

This is quite a unique DAX formula. If you can understand how to do these DAX calculations, then you’re doing very well with your Power BI knowledge. You are in a good place to work out other advance things especially when iterating logic is required.

Enjoy working through this one.

Sam

## Debugging DAX: Tips and Tools for Troubleshooting Your Formulas

One of the main reasons why businesses all over the world have fallen in love with Power BI is because...

## Practical Application of TREATAS Function in DAX

A hands-on project focused on using the TREATAS function to manipulate and analyze data in DAX.

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## 5 Strategy Tips For Modern Data Management

Data is the lifeblood of modern business. It is the raw material that fuels everything from...