Power BI Data Sets: Learn How To Detect Abnormal Behavior Using DAX

In this blog post, I will show you in detail how you can discover abnormal patterns of behavior within your Power BI data sets. This is not something that will immediately stand out when running analysis across your data so you have to think quite analytically about it. You may watch the full video of this tutorial at the bottom of this blog.

By thinking deeply about what you want to achieve and then setting up a data model that works for these types of calculations, you can obtain high quality results.

In this example, we’ll be trying to understand what a customer has done previously. We will also assess if recent purchases are normal or abnormal compared to what has happened in the past.

Table of Contents

Discover Outliers In Your Power BI Data Sets

Let’s think about this from a commercial perspective. Say we are an online retailer, an airline, or a supermarket and we have to implement a loyalty program.

We need to look at our customer data set to know how much a customer has actually purchased for each individual quarter versus what they purchased on the average.

All of a sudden, we see a massive increase or decrease in buying patterns or buying behavior. We want to understand why they are purchasing a lot more than usual, or why they are purchasing less than usual.

This way, we can create alerts in our CRM system and then let our sales team target these people by giving out discounts or coupons.

This is a really powerful insight that you can derive from Power BI. The value you can extract from your analysis can positively impact your business.

Unique Products Bought

To calculate the average products bought in a particular quarter, we will first need to work out the Unique Products Bought. For each individual customer, how many individual products did they buy?

We can find this out by saying that for any customer context, we will work out the DISTINCT COUNT of the product ID in the sales table.

This formula is going to give us every single unique product a customer has bought from us in the current context.

Given the formula above, when we click on Q1 of 2017, we can see that customer Aaron Day bought only 1 product.

Average Products Bought In A Quarter

This is the formula we will use to produce this particular insight. It is going to say on average how much a particular customer has bought in a quarter.

Average Products Bought In All Quarters

The next thing we need is a benchmark or a number to compare it to something so we can understand what is abnormal behavior.

We will then calculate the same insight, but we don’t look at any particular date. We will release any context on dates.

What this formula does is return the average unique products in every single quarter for a particular customer. This is how we achieve this particular insight.

So now we have two values. When we make a selection (ex. Q2 2017), the Average Products Bought in All Quarters column is never going to change because of the ALL Dates function. This function removes any filters we showcased from Dates.

With every selection we make, the Average Products Bought in a Quarter column is going to change.

By comparing these two columns, we can know what is normal, and what is not.

We can put what we’ve come up so far in a visual chart for a more in-depth analysis. We can very quickly see how many products our customers have bought in a particular quarter (right side) versus how much on average they buy (left side).

We will be able to see the abnormal behavior from our Power BI data sets and see which customers have increased their purchases. Who are these people, and why are they doing it?

Armed with the data we have gathered, we can then implement actionable strategies to replicate this.

We can amplify our marketing message to increase the purchases of those who are not producing abnormal behavior.

Conclusion

This is where an abnormal, outlier, or anomaly type of analysis adds a lot of value. Why? Because we want to understand if our customers are performing better or if the salespersons are selling more.

If we can find patterns of behavior that are positive for us in a particular segment of our data, we can replicate this across other segments.

The goal is to replicate advantageous customer behavior and apply it on other areas of the business.

This technique will tremendously help with increasing your analytical thinking around what you can achieve in Power BI.

These types of analysis and many more can be found in the Advanced Power BI modules at Enterprise DNA Online. For more details, check out the module below.

Advanced Analytics in Power BI

Enjoy reviewing this one.

Sam