# Calculate Average Per Customer Transaction Using DAX In Power BI

What I want to demonstrate in this tutorial is how we can calculate average sales, profits, or transactions per certain dimension inside of DAX in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

In this particular example we’re going to look at it from a customer’s perspective. We’re going to try and analyze what the average sales are we make per transaction per customer?

This is going to enable us to understand who our best customers are, but also who are our customers that come in and buy a substantial amount.

From here we can ultimately understand what the margins are we’re extracting per transaction from our customers. Are they good in some regions compared to other regions? Are they good for some products compared to other products?

We are going to look at the average amount of products purchased per transaction. Then, I’m also going to show you how to derive even more so you can find even more interesting insights based on this initial one. We’re going to branch out into other things and I’m going to show you how to do it efficiently.

## How To Calculate Average Per Transaction

First, we will work out a value per transaction by jumping into the Sales table. We have an order ID column on the left side.

So every order ID equates to every transaction in this particular table. We need to find a way to evaluate every single one of these transactions and essentially average up the sales that we have made for every single transaction.

This is going to give us â€“ depending on the context â€“ the average per transaction. This could be from a regional perspective, a customer perspective, or a sales person perspective.

Some data tables have an Order ID, and then within that Order ID, you might have a number of different transactions. Depending on what average calculation you want to do you’ll probably want to input that column into the calculations. First, let’s calculate average sales.

## Calculate Average Sales Per Transaction

Letâ€™s create a measure and call this one Average Sales per Transaction. I’m going to use the AVERAGEX function because this will allow us to do these averages by iterating through something. Within AVERAGEX, I’m going to use VALUES and put in my Order ID. Then, I want to average up the Total Sales for every single order.

Once I drag this measure with my Customer Name context, this is the table I come up with:

This will show us on average how much each person makes per transaction every time they come into a store.

## Using Data Bars

This is already a pretty good insight by itself, but we can make this look better using conditional formatting and data bars.

## Calculate Average Profits Per Transaction

We don’t have to stop here; we can we can go even further. We have some other core calculations like Total Profits and Total Costs. With these calculations, I can find out the average profits per transaction. All I have to do is copy and paste the measure I just used into a new measure, and instead of Total Sales, I’m going to put in Total Profits.

With this new measure, we can work out what our profits are out of every single transaction and then average those up.

I can just drag the new measure into my table to come up with new insights. For example, our customer Chris Fuller has greater profitability per transaction then Philip Foster, who actually made greater sales. This is a pretty good insight, right?

## Calculate Average Margins Per Transaction

We can also deal with average margins per transaction. We won’t be needing to reference anything from the table because we can actually just use measures within measures.

All we have to do is divide Average Profits per Transaction by Average Sales per Transaction, then input 0 as an alternative result. We also have to make sure that it’s formatted correctly.

Once I drag this into the table, you’ll see now why we have higher profits for Chris Fuller than for Philip Foster’s. Chris has higher margins compared to Philip.

This is a very interesting insight for this particular customer, as well as the rest of our customers.

What’s cool is that we can use this technique on any context. Currently, we’re just using a filter from our Customers table. If you think about it, we can use filters from any of these tables in our data model and see how things change.

## Average Margins Per Transaction Over Time

We can also take a look at our Average Margins per Transaction over time. I’ll just quickly whip this up using Month & Year and Average Margins per Transaction as values, then put them out into a graph.

We can see how the average margins change through time and see the seasonality.

## Other Insights

Overall, it is a lot easier now to see our high margin versus low margin customers. For instance, customer Juan Collins has a 40% Margin. This one sticks out clear as day.

We can also save filter to see which are the most profitable customers per transaction; this could possibly indicate the salesperson assigned to these parts is very good.

## Visualising The Data

We can look further into the insight using DAX in Power BI and determine our best customers on a regional basis. Is there something happening regionally? We can switch from a map visual to a filled map.

We can drill in and have a more in-depth look. In my example, New Hampshire has low margins while every other region is pretty evenly distributed.

You can also select customers from the table, and determine from this a subset of customers where the breakdown was.

DAX Calculations: Total Of Average Results
Power BI Analytics: Run Scenario Analysis On Average Order Size
Using Moving Averages To Showcase Trends in Power BI

## Conclusion

We can extract so many great insights when we calculate averages using DAX in Power BI. They also add a lot of value to what we might do within a business from a marketing perspective and allocating sales resources perspective.

You obviously want to be focusing on the clients who purchase the most at the highest margins. And through this type of analysis, you can align your resources to where you feel you’re going to optimize the best results.

In this tutorial, we worked on one thing and then branched out into lots of other things. You can do many calculations and techniques using DAX in Power BI, and find some really good insights.

This type of analytical work is so powerful. If you want to review more examples just like it, check out the Business Analytics Series module at Enterprise DNA Online. This module contains contents around solving real-world business problems using the best practices of DAX in Power BI.

All the best,

Sam

## Debugging DAX: Tips and Tools for Troubleshooting Your Formulas

One of the main reasons why businesses all over the world have fallen in love with Power BI is because...

## Practical Application of TREATAS Function in DAX

A hands-on project focused on using the TREATAS function to manipulate and analyze data in DAX.

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...