In this Power BI analysis example, we’re going to generate a unique insight where we’re going to evaluate all our customers and see how many unique products they bought. I’ll show you how to analyze this over time and use anything in the data model. You may watch the full video of this tutorial at the bottom of this blog.
For this Power BI analysis, we’ll evaluate the data in a customer context. What happens to the tables and behind the scenes is that the customers will be filtered in our Customers table. Then, the Customer ID will be filtered for each individual customer.
Then we have our Product ID here (inside the Sales table), which is going to be a column or a dimension in our table that we could then iterate through and see how many unique items are left after the filter has been put in place. Power BI has a great function called DISTINCTCOUNT that enables us to do this.
Using DISTINCTCOUNT For The Calculation
We’ll now create our formula, Unique Products Bought, and use the DISTINCTCOUNT function, which will go and find those unique products inside the Sales table. And then we can work out how many unique Product IDs there are.
If we bring this into our table, we now see all the unique products our customers have bought. This customer, Andrew Graham for example, bought 19 unique products from us. Note that we’re analyzing through time because we have no date filter here.
We could also sort this as who’s the highest or best customer, not from a revenue perspective, but who bought the most unique products. In this case, it’s Ralph Richardson with 29 items.
Using The Data Model For Further Analysis
We could analyze this even further. We could dive into any element in our model like so, where we create some additional context from the spatial visualization.
If we select a store, for instance, we’re going to see the customers with the number of unique items they bought from us from this specific store.
We could also set up another table with our Product names, Locations (our Store names), Customer name, and see the Sales of each product.
We can then select any customer and look at all the individual products that the customer bought.
The data set in this sample Power BI analysis is just random, which is not realistic. However, there are many ways to really dive into our data by using the elements in our data model, and this is just one example.
***** Related Links*****
Discover Multiple Product Purchases Using DAX In Power BI
First Purchase of Customer Insight Using DAX
Counting Customers Over Time Using DISTINCTCOUNT In Power BI
Conclusion
The key thing that I want you to grasp from this tutorial is understanding the context of the analysis, which in this case is the Customer context.
We used the Customer name table, then we worked out the Total Sales and evaluated the distinct count of the Product ID column after that context or filter was put in place. Through the formula we have created, we can then calculate all those unique items bought by our customers.
Hopefully you’re able to take some key information away from this and see how you can also apply such a relatively simple DAX formula into your own work.
Cheers!
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events
[youtube https://www.youtube.com/watch?v=KeapGs-VTYI?rel=0&w=784&h=441]
