This tutorial involves finding out your top customer per product using an advanced Power BI analysis. This example perfectly shows how Power BI can generate practical insights once you utilize it effectively. There’s just no other tool out there that’s as versatile and flexible as Power BI. You may watch the full video of this tutorial at the bottom of this blog.
In Power BI, you can quickly and easily get advanced insights that can add value to your business analysis.
Moreover, it’s all just a matter of understanding the DAX formula language and combining different functions together to be able to extract amazing insights.
This tutorial enables you to see the names of the top purchasing customers per product and get other insights based on the original list.
It’s interesting how you can achieve this by combining a few techniques. Once you become more familiar with the key concepts of Power BI, you can easily make your way through this example. For now, let’s work through the formula for calculating the top customer.
Calculating The Top Customer
First, click New Measure, and then rename the measure as Top Customer.
You need to do a ranking calculation to get the names of the top customer. Since you need to return a value, you can use either the MAXX or MINX function.
For this scenario, it’s best to use the MAXX function because you need to find the top purchasing customer. This function is an iterator, so you need to put a virtual table inside the calculation that should be based on ranking. The MAXX function here will rank the customers based on the revenue that they generated.
The next thing that you should add to the equation is the TOPN function. It’s going to put a virtual table inside of the table parameter and then return the top one.
You also need to create another summary table by using the SUMMARIZE function. This virtual table contains all the customers and their revenues. In the table, the first column will be for Customer Names and the second column is for Total Revenue.
Now, you need to establish an order for the customers. You just need to reference the Total Revenue column inside of SUMMARIZE. After that, you can sort the customers by revenue in a descending order so that the customers with the highest revenues are placed at the top of the list.
To return the name of the top customer, you need to reference the Customer Name column in the formula.
Once you’re done with the formula, bring it into the table to see the corresponding results. In the Top Customer column, you can see the list of top customer by revenue for each day. You can also click any part of the graph to view the top customer of a specific region.
This might be a tricky formula, but combining these techniques in a number of different ways can actually create lots of great insights. For instance, if you’re selling different products, you can find out your top selling day or top product.
Extracting Top Customer Data From Different Channels
You can also put this insight into a matrix and extract other customer data from different channels.
For instance, you can grab the channel dimension from the sales table and put it into the columns.
This is how the visualization would look after you put the Channel table into the columns.
There is a lot of product information and you can get the list of top customers from the different channels like Distributor, Export, Wholesale, and more.
For dynamic results, you can also click on the different regions to recalculate the top customers accordingly.
Working Out The Top Revenue
Lastly, you can try and work out your top revenue using the same pattern that you used for calculating the top customer.
Just copy the existing formula, and then create a new measure and rename it as Top Customer Revenue.
Using the copied formula, just change the Customers[Customer Names] part into Rev.
You can save it, and then bring it into the table to see the corresponding results under the Top Customer Revenue column.
***** Related Links *****
Find Top Customers Using RANKX In Power BI
Discover Unique Insights Using Power BI TOPN Function
Using Dynamic Visuals On Ranking Based Parameters In Power BI
Conclusion
Understanding and knowing your top customers is one of the key things to do to have a strong customer relationship.
I hope you got a lot of insights from the techniques that I have showed you. The formula is somewhat easy to replicate and works really well, so I hope you can apply it to your own business data.
Cheers to more learning!
Sam
