Home » Power BI » Hierarchical Axis and concatenate labels in Power BI

Hierarchical Axis and concatenate labels in Power BI

Concatenate labels in Power BI

In this blog I will show you how you can set format for hierarchical axis labels on chart in good way, also we will see how concatenate labels property will help us to achieve this.

Format issue with multi axis/ hierarchical axis data-

In below screen shot there are 3 level of hierarchy in Axis and they are – year, quarter & month.

You can see in below screen shot, chart is showing concatenation of year & quarter month in X-axis level. It’s look like very messy and not easy to read.

Hierarchical axis data

Hierarchical axis data

Generally It should be like this-

X axis formatting

X axis formatting

Let’s get started-

Download Sample dataset- Global_superstore_2016.xls

Follow these steps to create a small multiples:

Step-1: Import Orders dataset from Global superstore file. After load data into Power BI file, check the datatype for order date column, if datatype is not in date format then change it as in date.

Step-2: Add one clustered column chart, add drag columns as mentioned below-

Axis: Year, Quarter & month column from Order date.

Legend: Category

Values: Sales

By default it will display first level of hierarchy data on chart in X-axis. After that click on “Expend all down..” icon( red highlighted icon) to display one more level of hierarchy on chart. Similarly click to the last level of the hierarchy.

Hierarchy data on visuals

Hierarchy data on visuals

Step-3: Now all three levels of hierarchies are visible in x-axis.

Hierarchical axis data

Hierarchical axis data

Step-4: Now sort the chart data by year, quarter & month. click on ellipsis icon > Sort by > Year Quarter Month

Sort visual data

Sort visual data

Step-5: Make sure sort order should be ascending, again click on ellipsis icon> Sort ascending.

Sort visual data asc

Sort visual data Ascending

Step-6: Now turn off concatenate labels- Go to format pane> X axis > turn off concatenate labels.

Turn off concatenate labels

Turn off concatenate labels

Now you can see in above screen shot, its looks good and easy to read. Concatenate labels property does not works with all visuals like stacked bar chart, clustered bar chart & area chart etc.

Refer more Power BI Post – Power BI

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.

2 thoughts on “Hierarchical Axis and concatenate labels in Power BI”

Leave a Reply