Hierarchical Axis and 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.

Generally It should be like this-

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.

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

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

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

Step-6: Now turn off concatenate labels- Go to format pane> X axis > 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.