Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

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

by | 9:00 am EDT | May 01, 2020 | Power BI

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

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

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

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...