Clustering using Tables in Power BI
Introduction to Clustering in Power BI
Clustering in Power BI using Table Visual offers a simple yet powerful way to uncover hidden patterns in your data. Previously, we explored clustering with scatter charts. Now, we shift our attention to table visuals, which allow you to group similar data points based on meaningful similarities. This feature helps you quickly discover natural groupings before diving into more detailed analysis. Moreover, Power BI intelligently detects clusters and gives you the flexibility to customise clustering requirements to suit your data needs.
Let’s see what clustering looks like:
In its raw state, separating objects based on physical appearance feels nearly impossible. At this point, clustering becomes especially useful. It groups objects by identifying similarities among them and distinguishing them from dissimilar ones.
In real-world scenarios, such as a sales dataset, we often deal with a large number of products. Simply reviewing the data doesn’t make it easy to categorise these products by metrics like revenue or profit margin.
In this blog post, we’ll explore how to uncover these hidden clusters in your data. Furthermore, we’ll show how clustering can help you identify meaningful relationships that might otherwise go unnoticed.
Clustering in Power BI Using Table Visual
Clustering can also be performed in Power BI using a Table Visual.
Clustering can be performed in with:
- Scatter chart visual.
- Table visual.
- Custom visuals.
- DAX
In a previous blog post, we discussed clustering using a scatter chart.
For this post, we’ll work with a simple sales dataset. The data model includes a fact table (Sales_Table) along with three dimension tables: Product_Table, Country_Table, and Date. Additionally, we use a virtual table that contains the measures required for clustering.
Before we proceed further, let’s clearly define the business question we aim to answer:
What are the different categories of products in the raw dataset?
To address this, we’ll perform clustering using the Table visual in Power BI.
Step 1 – Create a Table Visual Create a table visual
- First, click on the Table Visual in the Visualisations pane.
Next, drag and drop the required fields into the Fields pane. - Then, drag and drop Product from Product_Table into the Values section.
- After that, drag and drop Revenue from All Measures into the Values section.
- Finally, drag and drop Profit Margin from All Measures into the Values section.
Step 2 – Generate clusters
5.First, click on the three dots (more options) in the top-right corner of the Table visual.
6.Then, select Automatically find clusters from the dropdown menu.
A pop-up window will appear. We will use the default values for this exercise.
7.Click OK.
Step 3 – Analyze the clusters
- Now, drag and drop the newly created field Product (Clusters) 2 into the Values section of the Table visual.
- Next, add the Product (Clusters) 2 field to a Slicer.
- Then, click on Cluster 1 to view the grouped data.
Six clusters have been automatically detected by Power BI using Enhanced Artificial Intelligence.
Application of Clustering in Power BI using Table Visual:
Clustering is widely used across various industries. Some common use cases include:
- Healthcare analysis: Identify groups of patients who show similar symptoms.
- Biology: Classify animals and plants that share similar characteristics.
- Marketing: Group customers based on similar purchasing behaviour.
- City planning: Categorise houses by property value, type, and other factors.
- Earthquake analysis: Detect and classify high-risk or dangerous zones.
- Insurance analysis: Identify potential fraud cases and clients with unusually high claim costs.
Conclusion:
Clustering uses an algorithm to group data based on hidden relationships that are often difficult to spot just by viewing the raw data. Fortunately, Power BI intelligently identifies these meaningful connections and forms clusters accordingly.
As previously discussed, you can perform clustering using scatter charts. In addition, Power BI allows clustering through table visuals, custom visuals, and DAX expressions. In this post, we focus specifically on clustering using the Table visual.