How to Calculate Average of 3 Columns in Power BI

Calculating an average across a few columns in Power BI seems like it should be simple, but it’s a bit different than your typical spreadsheet formula. Unlike Excel, where you can just highlight cells, Power BI requires a more structured approach using DAX (Data Analysis Expressions) or a data modeling trick in Power Query. This guide will walk you through several effective methods to find the average of three columns, from quick calculations to the most scalable reporting solutions.

Why Would You Need to Average Columns?

Before jumping into the “how,” let’s quickly touch on the “why.” Calculating the average of multiple columns is a common task in business analysis. You might be doing this to:

• Analyze Performance Over Time: Find the average monthly sales for Q1 by averaging January, February, and March sales columns.
• Consolidate Metrics: Calculate the average website traffic from different sources like SEO, Paid Social, and Email.
• Evaluate Scores: Determine an average product rating by averaging scores from different review platforms (e.g., Capterra, G2, Trustpilot).
• Track KPIs: Measure the average lead response time across three sales regions, each in its own column.

In all these cases, the goal is to get a single, consolidated number that represents a typical value across different categories or periods that have been structured as separate columns in your dataset.

Preparing Your Data for Calculation

A quick check before you write any formulas will save you a lot of headaches. Make sure the columns you intend to average meet two criteria:

1. They are numeric. In Power BI's Data view, look for the Sigma (Σ) symbol next to your column names. If it's not there, you might need to change the column's data type to a number (e.g., Whole Number, Decimal Number).
2. Decide how to handle blanks. What should happen if one of the three columns is empty for a particular row? Should it be counted as a zero, or should it be ignored? Your approach will depend on your business logic, and we’ll cover how to handle this in a later section.

Method 1: The Simple (but Inflexible) Calculated Column

If you're coming from an Excel background, this method will feel the most familiar. A calculated column adds a new physical column to your data table where the calculation is performed for each row. This is a quick way to see the row-by-row average directly in your table, but it comes with limitations.

Step-by-Step Instructions:

1. Go to the Data view by clicking the table icon on the left-hand pane.
2. Select the table you want to work with.
3. From the "Table tools" ribbon at the top, click New column.
4. In the formula bar that appears, enter a DAX formula like this, replacing the table and column names with your own:

Row Average = ('Sales'[Sales Jan] + 'Sales'[Sales Feb] + 'Sales'[Sales Mar]) / 3

Press Enter, and a new column named "Row Average" will appear in your table, showing the calculated average for each row.

Pros and Cons of This Method:

• Pros: It’s straightforward, easy to write, and you can see the results for every row.
• Cons: Calculated columns are static. They don't react to filters or slicers in your report. For example, if you filter your report to show only one product category, the calculated column value doesn't change - it was pre-calculated at the row level. This lack of dynamic behavior makes it a poor choice for interactive dashboards. It also increases the size of your data model, which can slow down performance on large datasets.

Method 2: The Better Way with a DAX Measure and AVERAGEX

For reporting and analytics, a measure is almost always the right choice. Measures are dynamic formulas whose results are calculated on the fly based on the context of your report - like filters, slicers, or a visual. The perfect function for this job is AVERAGEX.

The "X" in AVERAGEX signifies that it's an iterator function. This means it will go through a table you specify (row by row) and perform an expression for each row, and then finally, it will average the results of that expression.

Step-by-Step Instructions:

1. Go to the Report view by clicking the chart icon on the left-hand pane.
2. From the "Home" ribbon, click New measure.
3. In the formula bar, enter the following DAX formula:

Avg Sales 3 Months = AVERAGEX( 'Sales', ('Sales'[Sales Jan] + 'Sales'[Sales Feb] + 'Sales'[Sales Mar]) / 3 )

Now, when you drag this "Avg Sales 3 Months" measure into a card, table, or chart, it will show you the correct, dynamically calculated average that responds to any filters you apply.

Why is This Better?

Measures are lightweight and powerful. The calculation isn't stored in your table, so it doesn't inflate your file size. Most importantly, it's aware of the "filter context," meaning it will always show the correct average for whatever data slice you're looking at. For instance, if you have a slicer for "Region," the measure will automatically recalculate the average sales for only the selected region.

Method 3: The Best Practice - Unpivoting Columns in Power Query

While AVERAGEX is a great solution, the absolute best practice from a data modeling perspective is to reshape your data. Data that is "tall" (more rows) is often better for analysis in Power BI than data that is "wide" (more columns).

In this case, instead of having columns for Sales Jan, Sales Feb, and Sales Mar, you would transform them into two columns: one called "Month" and one called "Sales Amount." This process is called unpivoting.

Step-by-Step Instructions in Power Query:

1. From the "Home" ribbon, click Transform data to open the Power Query Editor.
2. In the Power Query window, select the table you are working with.
3. Hold down the Ctrl key and click on the headers of the three columns you want to average (Sales Jan, Sales Feb, Sales Mar).
4. Go to the Transform tab in the ribbon.
5. Click the dropdown arrow on Unpivot Columns and select Unpivot Selected Columns.

Power Query will magically transform those three columns into two new ones, likely named "Attribute" and "Value." You can rename "Attribute" to "Month" and "Value" to "Sales Amount" for clarity.

Click Close & Apply in the top-left corner.

The Final, Simple Calculation

Now that your data is perfectly structured, creating the average is incredibly simple. All your sales figures are in one column! You just need a basic AVERAGE measure:

Total Sales Average = AVERAGE('Sales'[Sales Amount])

Why Unpivoting is Worth It:

• Scalability: What happens when you get April sales data? With the old methods, you have to update your DAX formula. With the unpivoted model, you just refresh the data, the Total Sales Average measure doesn't need to change at all.
• Flexibility: Your visuals become much easier to build. Want to see sales trends by month in a line chart? Just drag the "Month" column to the X-axis and "Total Sales Average" to the Y-axis. Done.

Bonus: Properly Handling Blanks and Zeros

A common pitfall is how blanks are handled. If a row has sales of 100, 200, and a BLANK, the simple formula (100 + 200 + 0) / 3 gives you an average of 100. But what you probably want is (100 + 200) / 2, which is 150.

You can create a more robust DAX measure to handle this logic correctly. Here’s a powerful pattern using variables:

Robust Column Average = 
AVERAGEX(
    'Sales',
    VAR Col1 = 'Sales'[Sales Jan]
    VAR Col2 = 'Sales'[Sales Feb]
    VAR Col3 = 'Sales'[Sales Mar]
    VAR TotalValue = COALESCE(Col1, 0) + COALESCE(Col2, 0) + COALESCE(Col3, 0)
    VAR NonBlankCount = 
        (IF(ISBLANK(Col1), 0, 1)) + 
        (IF(ISBLANK(Col2), 0, 1)) + 
        (IF(ISBLANK(Col3), 0, 1))
    RETURN
    DIVIDE(TotalValue, NonBlankCount)
)

Here’s a quick breakdown of what’s happening in this formula:

• VAR lets us store parts of the calculation in named variables for readability.
• COALESCE(Col1, 0) is a safe way to treat a blank value as zero, ensuring the sum works correctly.
• ISBLANK() checks if a column is blank and helps us count how many of our three columns actually have a value.
• DIVIDE() is a safe division function that prevents errors if NonBlankCount happens to be zero.

Which Method Should You Choose?

• Calculated Column: Only use this for a quick, one-time check or if you specifically need a static, row-by-row value for another calculation. Not recommended for reporting.
• AVERAGEX Measure: This is your go-to for most interactive reporting needs. It’s dynamic, efficient, and strikes a great balance of power and simplicity.
• Unpivoting in Power Query: This is the gold standard. If you have the flexibility to shape your data, do this. It makes your DAX formulas simpler and your data model more scalable and robust.

Final Thoughts

Calculating the average of three columns in Power BI reveals a core concept of the tool: good reporting starts with good data structure. While you can brute-force the answer with a calculated column or craft a dynamic AVERAGEX measure, taking the time to unpivot your data in Power Query will pay off significantly in the long run by making your analyses faster, simpler, and more scalable.

We know that managing DAX and learning data modeling principles can feel like a steep climb, especially when you just need a quick answer. That's why we created Graphed. Instead of wrestling with AVERAGEX or transforming data, you can connect your sources and simply ask in plain English, "What was the average revenue from my January, February, and March campaigns?" It handles the technical work for you, instantly building dashboards and giving you the insights you need to get back to growing your business.