# How To Use Iterating Functions To Fix Total Issues In Power BI – DAX Concepts

I’m going to show you today how to use iterating functions when you’re trying to fix Total issues in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

When I go through the questions posted on the Enterprise DNA support forum for members, I see a lot of people having issues with the Totals they get on their report regardless if they’re using advanced logic or not.

Here, I’ll show you why that happens and how you can get around those issues.

## Computing Min Revenue Totals

Let’s use a simple example on computing Min Revenue Totals.

The data below shows that I have my Total Revenue as well as the Total Revenue Last Year.

Getting last year’s revenue is simple. I just used the DATEADD function.

The Total Revenue shows data for the year 2018. Then you’ll see that we subtracted one year under the DATEADD function, which gives us the 2017 data.

You’ll see that we also have another column after Revenue Last Year, which is the Min Revenue Year. Basically, this column should show the lowest numbers out of both 2018 and 2017.

Alternatively, you can also click on the legend and choose a different year. So you can use the revenue for 2016 and 2017, for example.

But for this example, let’s stick to the 2017 and 2018 data.

If you’ve done some work using Excel, you’ll see that the formula I’m using here is the same.

Once you hit enter, you can go through the numbers and see that as you work your way through the rows, the numbers make sense. That is, until you reach the Total at the very bottom.

You’ll notice that it shows the same Total as the Total Revenue column even if the data throughout the rows are different. This is definitely not the result we’re looking for.

To understand why this happened, you also have to understand the context. Here, the context only lies on the rows preceding the Total where it chooses the lower amount between the two columns. But at the end, it simply compared the two totals from 2017 and 2018, picked which one was the minimum amount, and took that as the result.

It doesn’t understand that you want the total of all the Minimum Revenue listed throughout that column.

## Using SUMX To Fix The Total

This is where iterating functions come in. For this example, I’m going to use SUMX.

Using SUMX as the iterating function, I can start working through the table virtually.

I’ll start by using the SUMMARIZE function for the Customers. Then, I’m using the MIN function to get the minimum value for the Total Revenue for 2017 and 2018.

When you use iterating functions, the calculation happening at every single row happens virtually.

All of these calculations are stored into memory, after which the SUMX function adds all of that stored data to come up with the right total at the bottom of that column.

In other words, it finally adds more context to make the system understand what kind of result you’re looking for.

## Conclusion

With this tutorial, hopefully, you were able to see how to fix Total issues you’ve been encountering with Power BI. The key here is understanding how iterating functions like SUMX work. Remember that these functions can be used to virtually work through each row of data, and you can get the right results.

So, if you get stuck on a Total in Power BI, you can play around with techniques like this. See if you can work in some parameters that follow the same context we applied to retrieve the value that you’re looking for.

All the best.

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...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...