Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Calculate Average Per Customer Transaction Using DAX In Power BI

by | 9:00 am EST | February 28, 2020 | DAX, 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

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...