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.
***** Related Links *****
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
[youtube https://www.youtube.com/watch?v=J8N4MG7U4Gg?rel=0&w=784&h=441]
