Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Segmentation analysis using Mekko Charts in Power BI Desktop


By:   |   Last Updated: 2018-01-19   |   Comments   |   Related Tips: More > Power BI

Problem

Stacked bar charts and stacked column charts are the fundamental charts for starting entry level segmentation analysis that involves categorical data. The shortcoming of stacked charts is that the total length or width represents the total of any given category. So, one can analyze segmentation only within a given category, but cannot analyze measures from a distinct perspective. A chart is required that allows us to analyze the weight of the overall category relative to other categories, along with its segments. In this tip, we will understand how to perform segmentation analysis using a unique chart in Power BI Desktop.

Solution

Power BI provides Mekko Charts in the Power BI Visual Gallery to perform segmentation analysis on categorical data.

Marimekko design or the Mekko Chart is a type of variable-width bar chart like a tree map or mosaic plot in which all the bars are of equal height, there are no spaces between the bars, and the bars are in turn each divided into segments of different width. Marimekko charts are generally used for categorical data analysis. You can read more about Marimekko here.

In this tip we will create Mekko Chart in Power BI Desktop using some sample data, using which we would analyze categorical data. We will also compare the stacked charts side by side to understand the advantage of using the Mekko Chart. It is assumed that Power BI Desktop is already installed on the development machine. The sample data used in this tip can be downloaded from here. Follow the steps as mentioned below.

1) The first step is to download the Mekko Chart from here, as it is not available by default in Power BI Desktop.

Mekko Chart - Description: Mekko Chart

2) After downloading the file, open Power BI Desktop. You can click on the ellipsis in the visualization tab and select ďImport from fileĒ menu option. This will open a dialog box to select the visualization package file, to add the visualization in Power BI. Select the downloaded file and it should add the mekko chart to Power BI Desktop visualizations gallery.

3) First, we need to import the sample data which we will be used to create the visualizations for data analysis. Select the Get Data menu as shown below, and select ďText/CSVĒ as our sample data is stored in a CSV file.

Get Data - Description: Get Data

4) This will open a dialog box to select the CSV file. Navigate to the path where you have stored the CSV file, select the file and click OK. This will open a dialog box as shown below. A few rows from the file are read to preview the data as shown below. Click Load to load this data into the data model.

Sales Data - Description: Sales Data

5) After the data is loaded in the data model, it should look like this in the Power BI Desktop as shown below.

Data Model - Description: Data Model

6) Letís first use stacked column chart to analyze the data that we just loaded. Click on the stacked column chart and this will add the control on the report layout as shown below.

Stacked Chart - Description: Stacked Chart

7) Select the data fields: Company, Region and Sum of Sales and those will be added in order to the Axis, Legend and Values section as shown below.

Stacked Chart - Description: Stacked Chart

8) As you can see, there are three distinct values in the Company axis, and five distinct values in the Legend axis. The height of the assorted colors represents the proportion of sales. But itís not clear how much Company A, B and C contributes to the overall sales of the organization comparatively. Also, one piece of detail that is not explicit here is the proportion or percentage of Sales. Navigate to the format menu, and turn on the data labels as shown below, so that we can see the exact percentage of each item within a category.

Stacked Chart - Description: Stacked Chart

9) Similarly, add a stacked bar chart on the side and populate this with the exact same detail using the same steps explained above. This will create the chart as shown below.

Stacked Chart - Description: Stacked Chart

10) One detail that is not clear from both of these charts is how much is the contribution of one company in the overall sales. Now letís add a Mekko Chart that we added during the start of this exercise, and populate it with the exact same data using the steps explained above.

Mekko Chart - Description: Mekko Chart

11) Until now, the proportion looks the same. Add the Sum of Sales field again to the axis-width section as well. This will change the visualization as shown below. From the below visualization of the Mekko Chart, it is easy to identify the weight of each sales segment in the overall sales. Now it is easy to make out from the Mekko Chart that the overall contribution of Company A is the highest in overall sales.

Mekko Chart - Description: Mekko Chart

12) Letís make this more interactive by turning on the data labels from the format section. Now the visualization should look as shown below. By making a combination of a stacked bar and a stacked column chart, the Mekko Chart resembles a tree map.

Mekko Chart - Description: Mekko Chart

13) One can also use a different measure for the width axis to render the width of each segment. Below is an example of this. In this visualization, vehicle manufacturers represent the categories, each color in the bar represents the type of vehicle, the value of the vehicles is represented by the height of color, and the overall sales by the manufacturer is represented by the width of the bar. Derivation can be easily made from this visualization, like for example, Honda has the largest sale of domestic cars compared to other vehicles, but the overall sales of Honda is probably the lowest compared to other manufacturers.

Mekko Chart - Description: Mekko Chart

One point to note is that although the Mekko Chart solves a visualization design problem, it suffers from the same problems as stacked charts. It is not easy to visually compare each segment if the similar segments are not lined up side by side. You can read more about the limitations of Mekko Charts from here. Itís advisable to understand the use case first and then apply Mekko Charts for segmentation analysis, as there are other charts like mosaic plot, tree maps, heat maps, etc. for diverse types of segmentation analysis.

Next Steps
  • Try modifying the legends, data colors, and data labels for a truly customized presentation of the Mekko Chart.


Last Updated: 2018-01-19


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools