Do you need to calculate weekly averages in Power BI?

In the realm of data analysis, deriving meaningful insights often involves aggregating data into different time frames for better understanding.

Calculating a weekly average is a common requirement in many business scenarios, offering a condensed view of trends over time.

This blog aims to help you in the process of creating a Weekly Average measure in Power BI, enabling you to reproduce this methodology for diverse time spans or dimensions effortlessly.

Create a Proper Calendar Dimension Table

The key to proper time intelligence measures is to have a calendar dimensions table and build proper relationships with your fact tables.

There are several ways to create the calendar table. We will delve into one of the ways that utilizes Power Query.

1. Go to Transform Data in Power BI desktop to open the Power Query window.

2. Click on Manage Parameters and then choose New Parameters to create a new parameter for the start date of your calendar.

3. Call the parameter Start Date.
4. Choose Date as the Data Type.
5. Input the date that you want as the start date of your calendar.

= #"Start Date"

6. Create a blank query and rename it “DimCalendar”. Input the above in the formula bar.

7. Change the value To Table.

8. Add a custom column for today as the end date.
9. Change the datatype of both columns to “Date”.

10. Select both columns and go to the Add Column tab. Open the Date options then select Subtract Days.
11. Create a new custom column to generate a list of dates between the start date and end date using the formula below.

List.Dates([Column1],[Subtraction],#duration(1,0,0,0))

12. Expand the list and delete the other unneeded columns.

13. Add your other dimension columns, like start of month and start of week, for the purpose of this article we will only need start of week.
14. click on Close & Apply.

You can follow the above steps or create the calendar using any other alternative way. But make sure you have a start of week column to use for the weekly average calculation.

Create a Relationship Between the Calendar Table and the Fact Table

A proper relationship between your tables is crucial for your report. In this case we need a proper relationship between the DimCalendar table and the fact table.

For our example we will be using a sales data table for getting the weekly average of order quantity.

1. Go to the Modelling tab.
2. Open the Manage relationship window.
3. Create a new relationship.
4. Choose the Dates column in your DimCalendar table and the main Dates column in your fact table, in our example it is the OrderDate column in the Sales Data table.

Create a Weekly Average Measure in Power BI

The important concept to understand here is the granularity of the data we are dealing with.

In our example we want to know the average quantity sold each week.

If you simply create an average of order Quantity, the formula will be going row by row on the sales data table. Which will return the average quantity per order, since this data granularity is on the order number.

To calculate the weekly average, you will need to change the granularity of the data inside your measure.

Its important to understand what you want to achieve, here is how the logic should go:

Sum order quantity for each Week then take the average of the weekly order quantity

Create a new measure using the DAX formula below to calculate the weekly average:

Weekly Average = AVERAGEX(VALUES(DimCalendar[Start of Week]),CALCULATE(SUM(SalesData[OrderQuantity])))

Let’s walk through each part of the formula to get a better understanding of what you are building here:

1. Why the AVERAGEX function? This function is one of the iterator functions, which will allow you to iterate through the weeks in your data. The function takes a table as its first argument, and an expression as the second argument.
2. Why The VALUES function ? it returns a table of unique values from the specified column. In this case it is returning a table of unique weeks in from our calendar table. This will allow you change the granularity to a weekly level.
3. Why the CALCULATE function? This is important to allow the context transition to happen.
4. What is context transition? Context transition transforms any existing row context into an equivalent filter context. The context that we are trying to transition is to change the granularity from the Sales Data , Order Granularity, to the Calendar table, Day Granularity.
5. Why is there a SUM? Remember, you want to sum the order quantity week by week.

In essence, you are telling Power BI to sum the order quantity week by week and then take the average of the result.

Visualize the Weekly Average Measure

For this measure to show you correct results, you will need to use a visual which summarizes data with a time range wider than the week, for example on a monthly, quarterly, or yearly basis. You can even use it on a card visual.

Conclusions

In the world of data analysis with Power BI, understanding how to calculate a weekly average or any time intelligence measure requires the understanding of how DAX operates. This guide walks through the process step by step, making it easy to create a Weekly Average measure using Power Query and DAX formulas.

Firstly, it stresses the importance of setting up a detailed calendar table, showing how to do this precisely to ensure accurate analysis. It then highlights the significance of linking tables correctly, demonstrating how to establish these relationships for better data handling in Power BI.

The heart of the guide lies in explaining data granularity—essentially, how detailed your data is—and how to adjust it to get the insights you need. Breaking down the DAX formula, it simplifies the logic behind calculating the Weekly Average measure, empowering you to maneuver through different data granularities to get accurate results.

Overall, this guide is a handy resource for anyone seeking to compute weekly averages and understand the nuances of data manipulation in Power BI, paving the way for informed decision-making based on clear data insights.