How to Show Average in Power BI

Calculating an average is a fundamental task in any data analysis, and Power BI offers several ways to do it. You can get a quick average with just a few clicks, or write powerful DAX formulas that give you complete control over your calculations. This tutorial will walk you through the most common methods, starting with the simplest and moving to more flexible and powerful techniques.

The Quickest Method: Using Implicit Measures

The fastest way to find an average in Power BI is by using what's known as an implicit measure. This happens when you drag a numeric field onto a visual and Power BI automatically guesses how you want to summarize it (usually by summing it). You can then easily change this summarization to an average.

This method is great for quick explorations but isn't considered a best practice for reports you intend to maintain or share, which we'll cover in the next section.

How to Do It: A Step-by-Step Example

Let's say you have a sales table and you want to see the average sale amount per product category.

1. Drag a categorical field (like Product Category) and a numeric field (like Sale Amount) onto your report canvas. Power BI will likely create a bar chart for you.
2. By default, Power BI will sum the Sale Amount. You'll see this in the Y-axis field well under the Visualizations pane. It will probably read as "Sum of Sale Amount."
3. Click the small downward arrow next to "Sum of Sale Amount."
4. In the menu that appears, select Average.

That's it! Your chart will instantly update to show the average sale amount for each category instead of the total sum.

Pros and Cons of Implicit Measures

• Pros: Extremely fast and intuitive. Perfect for when you're just exploring the data and need a quick answer without writing any code.
• Cons: Not reusable. If you want to show the average sale amount in three different charts, you have to repeat these steps three times. It also offers limited flexibility if you need to calculate averages based on more complex conditions.

The Right Way: Creating an Explicit Measure with DAX

For building robust and scalable reports, the best practice is to always use explicit measures. You create these using Data Analysis Expressions (DAX), which is Power BI’s formula language. It might sound intimidating, but for a simple average, it's very straightforward.

An explicit measure lives in your Fields list and can be reused across any number of visuals in your report. If you ever need to change the formula, you only have to do it in one place, and every visual using that measure will update automatically.

Simple Averages with the AVERAGE Function

The most common DAX function for this is AVERAGE. It does exactly what you'd expect: it calculates the average (arithmetic mean) of all numbers in a column.

How to create the measure:

1. In the top ribbon, go to the Modeling tab.
2. Click on New Measure. A formula bar will appear at the top of the screen.
3. Type your DAX formula. The structure is Measure Name = FUNCTION('TableName'[ColumnName]). For our example, it would be:

Average Sale Amount = AVERAGE(Sales[Sale Amount])

Once you press Enter, you'll see "Average Sale Amount" appear in your Fields pane, usually with a small calculator icon next to it. Now you can drag this measure into any visual, just like you would with a regular field. The result is the same as the implicit measure, but you now have a reusable calculation.

Conditional Averages with the AVERAGEX Function

What if you need to calculate an average based on a calculation that happens row by row? For instance, maybe you want to find the average profit per transaction, but you don't have a dedicated "Profit" column. You only have "Sale Amount" and "Cost of Goods Sold (COGS)."

This is where the AVERAGEX function comes in. It’s an "iterator" function, which means it evaluates an expression for each row of a table and then averages the results.

How to use it:

1. Click New Measure from the Modeling tab.
2. The syntax is Measure Name = AVERAGEX('TableName', expression). Your formula would look like this:

Average Profit Per Transaction = AVERAGEX(Sales, Sales[Sale Amount] - Sales[Cost of Goods Sold])

In this formula, Power BI goes through every single row in the Sales table, subtracts the cost from the sale amount to get the profit for that row, and then calculates the average of all those individual profit results. This is extremely powerful because you don't need to clog up your data model by adding a new calculated column just for this one metric.

Adding an Average Line for Visual Context

One of the most effective ways to show an average is by adding a constant reference line to a chart. This allows you to quickly see which data points are above or below the overall average, providing immediate context.

For example, you could have a bar chart showing total sales by salesperson. By adding an average line, you can instantly identify your top performers relative to the team average.

How to Add an Average Line

1. Select the visual you want to modify (a column or bar chart works best).
2. In the Visualizations pane, click on the magnifying glass icon, which is the Analytics pane.
3. Expand the Average Line section and click + Add line.
4. By default, Power BI will add a line based on the measure already in your chart (e.g., Sum of Sales). You can leave this as is if you want the average of the items shown in the chart.
5. Alternatively, you can select a different measure from the Measure dropdown to base the line on. For instance, you could use the Average Sale Amount explicit measure we created earlier.
6. You can customize the line’s color, style (dashed, dotted, or solid), transparency, and choose whether to display a data label.

Advanced Averages Using CALCULATE and ALL

Sometimes you need to compare a value against the average of everything, ignoring the filters currently applied in the visual. For instance, in a chart showing sales by product category, you might want each category's bar to be compared against the average of all sales, not just the sales in that category.

This is where the combination of CALCULATE and ALL becomes your superpower. CALCULATE modifies the context in which a calculation is made, and ALL removes filters from a column or table.

Example: Creating an Overall Average Measure

Let's create a measure that calculates the average of all sales, regardless of which product category, salesperson, or region is being shown in a particular visual.

1. Create a New Measure.
2. Use the following DAX formula:

Overall Average Sales = 
CALCULATE(
    AVERAGE(Sales[Sale Amount]),
    ALL('Sales')
)

Here's what’s happening in this formula:

• The AVERAGE(Sales[Sale Amount]) is the core calculation we want to perform.
• ALL('Sales') tells CALCULATE to remove any and all filters that might be applied to the Sales table. In a bar chart broken down by category, the "category" is a filter. ALL tells Power BI to ignore it.

Now, if you create a table visual and add Product Category, your original Average Sale Amount measure, and this new Overall Average Sales measure, you’ll see something interesting. The "Average Sale Amount" will be different for each category, but the "Overall Average Sales" will show the same number all the way down the column - giving you a perfect baseline for comparison.

Final Thoughts

Knowing how to show an average in Power BI is a core skill that opens the door to more meaningful analysis. By moving from simple implicit measures to reusable DAX formulas like AVERAGE, AVERAGEX, and CALCULATE, you gain the ability to create reports that are not only accurate and insightful but also scalable and easy to maintain.

While mastering DAX can be an incredibly rewarding skill, we know that many marketing and sales teams don't have the time for its steep learning curve - they just need reliable answers, fast. We built Graphed to remove this friction by connecting all your data sources and allowing you to create dashboards using simple language. Instead of writing formulas, you can just ask questions like, "What was our average revenue per customer from Facebook Ads last quarter?," and get a live, automated dashboard in seconds.