Data Normalization in Power BI for Different Days Results

by Sam McKay, CFA | Business Intelligence, Power BI

This tutorial stems from another question on the Enterprise DNA Forum about data normalization. The techniques that I will teach you will be important in getting rid of anomalies that can possibly make things complicated during the data analysis. You may watch the full video of this tutorial at the bottom of this blog.

Occasionally, when running analysis in Power BI, you may need to take into account the sales for weekends or public holidays coming from different countries. The challenge is when it’s a public holiday for a certain country, but not in other countries. It’s going to be hard to normalize this sales data during reporting considering the different days of the countries. That’s where data normalization techniques comes into play.

The goal is to get all the different sales and place them back on the actual working days of the reporting country.

This is quite a unique example of a time intelligence technique in Power BI. Nevertheless, it’s a very useful one if you are dealing with a specific scenario around limited data or no data appearing in your raw information.

Table of Contents

Sample Data Normalization for Sales on Different Days

The most important thing that I want to go over in this tutorial are the ways to write some logic in your formula to change the position of a reporting day or the results on a particular day.

Let’s look at this table and really focus on an example. If you’re working with something similar where you have to move the results from one day to another, this tutorial will help.

In the report, you can see the date as well as what day it is in the Date and DaysOfWeekName columns. You’ll also be able to view the total number of sales for that specific day in the Total Sales column.

Now, I have worked out a logic to move the sales data of Saturdays and Sundays into the sales data of Monday. This is because I want to register those weekend sales into a weekday workday sales (Monday to Friday). You can see the updated sales data in the Total Sales Non Weekend Days column below.

Additionally, I wanted this table to be dynamic so I’ve added a data selector wherein you can easily change the time frame of the sales data. After selecting a respective time frame, the data in the Total Sales Non Weekend Days column updates automatically.

On top of that, I want to branch out into some other calculations like cumulative totals, moving averages, and so on. As you can see, I have added the Cumulative Total Adj. and Cumulative Sales columns. I just had to rearrange things slightly and get into some DAX calculations to solve these very specific insights.

Working Out The Non Weekend Sales

Now, I’m going to show you how to work out and set up the calculation for the non weekend total sales. This is the key thing here for this data normalization tutorial.

I have a couple of variables (VAR) here for Weekend Check and WorkdayCheck calculation. What I’m doing here is identifying which ones in the particular context are weekends and which are Mondays.

Jumping back to my date table, I’ve created a Day Type column using a very simple logic. So, if the day isn’t Saturday or Sunday, I’ll call it Workday. If it’s a Saturday (6) or Sunday (0), I’ll call it Weekend.

Now, after the first part of the formula, I’ll discuss this part.

So, if the WeekendCheck is true, meaning it’s a weekend, then the results for that are set to blank. Then, if the WorkdayCheck is equal to false or if the selected value equals to Monday is false, then it returns the Total Sales.

If the WorkdayCheck is true, meaning it is a Monday, I’m going to run this logic here.

This part is a combination of CALCULATE and FILTER functions. Firstly, it’s going to get the sum of the total sales for Monday as well as the weekend sales. The FILTER function is going to go over all the dates and will only return those dates that are greater than the current date subtracted by three.

Basically, I’m just moving the total sales from Saturday and Sunday into Monday using a combination of techniques like IF statements and various functions. That’s how data normalization works for this type of analysis.

The same technique will be very useful when you’re working on holidays or something like that. You just need to change some things here like the HolidayChecks instead of WeekendChecks. Nevertheless, it’s a similar logic and you can always apply this solution to those scenarios.

Creating A Cumulative Total

The next thing that I want to teach you is about creating a cumulative total of total sales and total non-weekend sales.

One thing you’ll be able to notice is the cumulative total data for weekends in this column will always be a flat line because there’s actually no data there no matter what your time frame is.

In connection to that, I’ve created a visualization that compares the cumulative total adjustment vs cumulative sales.

This is another interesting technique that you can utilize in a number of days when normalizing your data. Let’s look at the formula below.

Firstly, I’ve created an adjustment sales table by recreating the Date and Total Sales Non Weekend Days columns with the SUMMARIZE function.

After that, I’ve added the SUMX function. As you can see, the adjustment sales table is placed inside a FILTER function. Instead of going through all the dates, I’ve placed adjusted total sales non-working days. Lastly, using SUMX , it will iterate every single day and work out the date less than or equal to the max date. It will then count up the columns for Normalized Sales.

This data normalization technique is very applicable when you’re working across multiple countries. As you can see, it’s really an effective way to normalize sales across all the different regions into the report.

Conclusion

I have mentioned a lot of useful techniques in the tutorial that will enable you to run some unique time-related pieces of analysis inside Power BI. I went through a number of different DAX functions like the SUMMARIZE function, some table functions and other simple filters with the CALCULATE function.

If you want to check out more around a variety of different time intelligence techniques inside of Power BI, I recommend having a look at our course module in Enterprise DNA online which specifically caters to these types of analyses.

Time Intelligence Calculations

Enjoy working through this detailed video.

Sam