Calculating Average in Power BI: Isolating Weekday or Weekend Results Using DAX

Calculating an average in Power BI can be done in many ways to give you precise information for your business reports. You may watch the full video of this tutorial at the bottom of this blog.

However, sometimes calculating average sales per day may not be enough. You might run into a scenario where you only sell during weekdays.

If you run an averaging pattern in Power BI and you utilise the date table, it will run through every single day of the week, which is seven days.

In this case, your average will be lower than it should be because you will have days that will have zero results, which are days that you donâ€™t sell.

In this example, Iâ€™m going to show you how you can solve this issue. We will have the scenario where you only sell during the working week, which means the weekend is not included.

Therefore, we will calculate the average sales only on weekdays.

We will then calculate the average sales per weekend and make a comparison of the weekday and weekend average sales. I will show you how to compute for the average sales difference as well.

But before we dive into the main topic, I’m going to show you how we work out the average sales per day, which I discussed on a previous tutorial.

Average Sales Per Day

Calculating the average sales per day is very simple. We just put it in via a measure, using the AVERAGEX function.

This formula can show us the average sales per day by products, location, customers, etc. Just choose from the options and drag it to the axis. Here’s how it looks like:

This is a very helpful calculation. However, if you are only selling during weekdays, then you need to have another formula to get specific results.

Average Sales Per Weekday

To compute for the weekday average sales, we need to create a date table that doesnâ€™t have the weekends in it, iterating only over five days (Monday-Friday).

By utilizing the FILTER function, we can actually reduce the size of the date table virtually and then run this iterating logic through it via an AVERAGEX formula.

We create a new measure and call it Average Sales per Weekday. On the next line, we key in VAR for variable. In this formula, we use variables, as it simplifies everything.

We will call this variable the WeekdayTable, and we put tables inside by using the FILTER function.

Then, key in DATES, which iterates through the entire week, and choose the column DAY IN WEEK that has each day of the week represented.

Then, type in <> to exclude Saturday and Sunday, where Saturday is represented by number 6 and Sunday by the number 0.

Therefore, the date table iterates through the dates, but does not include Saturday and Sunday.

Once we have this table inside the variable, we can then integrate it into our calculation.

Using this formula, we can now create a visualization. Here we see both visualizations of per day and per weekday average sales.

We can also layer the two visualizations on top of each other, putting them into one visualization, which shows the comparison of sales.

Now, let’s work on calculating the Average Sales per Weekend.

Average Sales Per Weekend

When computing for the average sales per weekend, we simply copy and paste the formula that we have just created for average sales per weekday into a new measure.

Then, change weekday to weekend, <> (does not equal) to = (equal), and instead of &&, you change it to || (or).

And, we can also compare the two visualizations (per weekday and per weekend) and see the difference.

But, if you want to be more specific with the difference in sales on weekdays and weekends, then we use another formula, which is the average sales difference.

Average Sales Difference

To get the average sales difference, we create a new measure. For this formula, we simply subtract Average Sales per Weekend from Average Sales per Weekday.

This formula can show us the difference in sales, say per product. We can determine which products sell better than the others on a weekday or weekend.

Here you can see that product 81 sells better than product 65.

Conclusion

In the blog, we worked on calculating the average sales per weekday and weekend. We started by reviewing the formula for calculating average sales per day. We also use visualizations to make comparisons.

You can learn a lot from this example. One of the most important concepts to understand in Power BI is how to use virtual tables inside of iterating functions.

Iterating formulas with virtual tables inside is a great concept to learn because there are many applications where this would be relevant in your own analysis that youâ€™re doing.

Once you understand how iterating functions work, it opens up a world of analytical potential and your formulas become that much more simplified, as do your data models.

Thanks,

Sam

Power BI Trend Analysis: Are Margins Expanding Or Contracting?

In this blog post, I will be diving into a relatively specific insight by conducting a Power BI trend...

Data Visualization Report Frameworks | Part 2

For today, we'll be continuing the discussion on our enhanced visualization frameworks for Power BI....

Create A Power BI Sparkline Chart In Report Builder

In this Power BI Report Builder tutorial, youâ€™ll learn how to add a sparkline chart in your paginated...

Using Iterating Functions SUMX And AVERAGEX In Power BI

One of the most crucial topics for any Power BI beginner to know about is iterating functions. You may...

PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

Data Modeling In Power BI: Tips & Best Practices

In this tutorial, you'll learn valuable tips and best practices for data modeling in Power BI. You can...

Using The Query Builder Feature In DAX Studio

In this tutorial, youâ€™ll learn how to use the Query Builder feature to easily create queries and...

Time-Related Insights From Your Supply Chain Metric

For this tutorial, I'm going to cover some high-quality time-related insights directly from your supply...

Create A Multilingual Power BI Report

For today's blog, I want to discuss a not-unusual situation with many of my consulting assignments for...

CROSSJOIN DAX Function: Server Timings & Query Plan

In this tutorial, youâ€™ll learn how the CROSSJOIN function works in DAX using the Server Timings pane...

Overview Of The DAX Studio Keyword COLUMN

Another important keyword to learn when using DAX Studio is the COLUMN keyword. In simplest terms, the...

Python Cheat Sheet: Essential Quick and Easy Guide

When programming in Python, having a reliable cheat sheet by your side can be a game-changer. Python...