Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# 4 Techniques To Re-Use Moving Averages In Power BI

by | 9:00 am EDT | May 08, 2020 | Business Intelligence, DAX, Power BI

In this tutorial, I’m going to teach you how to highlight trends by using moving averages in Power BI in various ways. You may watch the full video of this tutorial at the bottom of this blog.

We can calculate one moving average and easily transfer the same formula into calculating other moving averages. All that’s required is changing one parameter inside the formula.

This is a technique which I talk a lot about because it is such a valuable idea and developmental concept inside Power BI. It definitely speeds up looking for a variety of insights that you may be looking for in your data. Especially if you are looking for a way to easily showcase the underlying trends in your data without diving away the day to day data.

This tutorial is also a great review of how you can utilize measure branching inside of Power BI.

In this sample report for Quarterly Insights, I have actually created different moving averages beforehand. I want to teach you how to reuse these formulas relatively quickly to achieve additional insights.

## Calculating Moving Averages For Total Sales in Power BI

Let’s look at this calculation for Sales 1M MA. The AVERAGEX function here looks at any particular day, and opens a time window in the future. After that, it calculates the average of all the results in that window. That’s because of the DATESINPERIOD and LASTDATE functions that I have also added.

Since it’s calculating for Total Sales, the formula looks at the total sales for every single day of the last month and works out the average of all those sales per day. That’s what this formula is doing.

Another great thing is that you don’t need to recreate the formula every time. You just have to copy and paste some existing formula and rearrange them. The first one actually calculates the total sales as you can see from the insight in the report.

If you look closer, you’ll see that you’re looking at the total sales per day. But you’ll also notice from the visualization that I have overlaid the average trend.

As shown, the average trend for the total sales is increasing slightly based on the particular time frame that I have selected.

## Finding Out the Profit Margin Average

The same can be seen with this formula here for Margins 1M MA. It’s a moving average of the margins that we have been achieving.

This formula is doing exactly the same logic from the previous sample calculation. The only thing that is different is instead of Total Sales I now have Profit Margin.

Now, if you dive deeper into the data, you can see that the margins for every single day are jumping around quite a bit. This can depend on many things like regions or products. But in this case, you can clearly see the moving average of the profit margin and it’s a bit dynamic.

## Calculating The Average of Total Profits

Now, there’s an additional technique that I want to show you. I actually recreated the profit margins moving average and copied it into another measure called Profits 1M MA.

You can see that I have just changed the last part from Profit Margins into Total Profits. So this moving average will be about the total profits for a particular time frame and I can then add this into my visualization.

Just drag it down to the report and then do a few things to clean up the look, color, shade, and everything else. Now, if you look at it, it’s also increasing slightly while the profits per day are jumping around a bit.

## Moving Averages in Power BI for Total Customers

The last thing that I’m going to teach you is about the moving averages of Total Customers for a particular time frame. As you can see in the sample report, the total customers for the second quarter of 2017 is 175.

If you look closely at the data, the number of customers are actually changing day by day. I can say that the trend here is very dynamic.

Now, let’s get to the formula. To create a new moving average for this, just copy the existing formula then click New Measure. Paste the formula and then change the old title to Customers 1M MA. Lastly, change the last part from Total Sales to Total Customers

That’s all you can do to reuse the formula for moving averages in Power BI. Simply amazing technique right here. After that, you can then add it to your report and improve the visualization.

Now, you can see that I’ve got a moving average trend line representing the total customers per day.

What I have just taught you is a great way to show moving averages more effectively. At the end of the day, you don’t want your trends to combine with all the other things in Power BI. By utilizing these moving averages in Power BI into a great visualization, you can always get the right insight.

## Conclusion

So, from maybe creating one moving average calculation, you can easily generate four or five in under a minute.

What I just covered came from a session that I run during the Enterprise DNA Learning Summit. If you would like to register for the summit, you’ll get access to a free three-day event, six sessions, and you can download many high-quality resources that demonstrate a number of advanced analytical ways to utilize Power BI. You can check this event for more details.

Enjoy working through this tutorial and see if you can work this logic into your model as soon as you can. You should see the benefits very quickly.

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

## How Many Rows Can Tableau Handle?

Are you dealing with a large data set? Are you wondering how many rows can Tableau handle efficiently?...

## How to Update Tableau in 4 Quick Steps

With frequent updates and new releases, Tableau continues to evolve and improve its functionality. To...

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

## What is Context Filter in Tableau? How to Apply and Set

One of the powerful features of Tableau is its ability to allow users to interact with data...

## How to Number Columns in Excel Automatically: 5 Quick Ways

In Excel, numbering columns is an important and straightforward process that can help you organize and...

## How to Make a Logo Transparent in PowerPoint: Quick Guide

A transparent logo can be a powerful tool in your branding arsenal. It allows your logo to seamlessly...

## Excel Waterfall Chart: Guide to Create, Design & Impress

Have you heard of an Excel waterfall chart but are unsure how to create one and make it stand out?...

## Tableau Tooltip: A Design Guide for Customization

Are you ready to use Tableau tooltips to level up your visualizations and make them more insightful and...

## What is a Set in Tableau and How to Create Them: User Guide

Are you getting started with Tableau and looking to create a set? Well, you're in the right place. In...

## How to Alternate Colors in Excel: 3 Top Ways Revealed

Knowing how to alternate colors in Excel can be a game-changer; there's little more satisfying than a...