Create Trellis Bar Charts in Power BI using R

What are trellis bar charts?

A trellis chart is a way to display smaller versions of your data across a categorical variable with the same scale and axes.  People commonly refer to these graphs as a lattice, small multiple, grid, or panel charts.  Ggplot2 users frequently refer to this style graph this as a facet plot.

This post will review why you should consider this type of data visualization, options for creating them, and then providing an example using Ggplot2 inside Power BI.

Why are they important?

These graphs help us solve what Stephen Few describes in his book “Now you see it” as the problem of “over-plotting.”  Too many data points within a given space can make the graph illegible to readers.  Reducing the amount of information when you plot is critical to creating impactful data visualization.


The Excel chart below encompasses several of the most grievous cardinal sins of data vizulaization. 


1.       Parts of the graph overlap.

2.      The reader can not easily determine how individual data points are important relative to other observations.

3.      3D effects and colors do not add value.

What solutions are currently available in Power BI?

If you would like to create a better version of this graph in Power BI, there are currently three options available.

1.       Decisive Data discusses the native option here.

2.       The Stacked Column Chart by Akvelon is free and has a small multiple feature.

3.       Create a custom R visual using Ggplot2.

All of these options have their own merits. My personal preference is option 3 due to R’s ability to customize the look and feel of the graph in a way not possible with other options.

R Code and screen shot of sample Power BI report


## Order Months in Chronological Order
dataset$Month = factor(dataset$Month, levels = c("January", 
    "February", "March", "April", "May", "June", "July", 
    "August", "September", "October", "November", "December"), 
    ordered = TRUE)

My_Facet_Plot <- ggplot(data = dataset, aes(x = Month, 
    y = Sales, label = Sales))

## color fill and black outline
My_Facet_Plot <- My_Facet_Plot + geom_bar(stat = "identity", 
    fill = "#E69F00", color = "black")

## Create grids/facets along dimension 'Year'
My_Facet_Plot <- My_Facet_Plot + facet_grid(~Year, 
    scales = "free", space = "free")

## Centered title at top
My_Facet_Plot <- My_Facet_Plot + labs(title = "Values by Year and Month")

## Text Wrap of titles on x axis
My_Facet_Plot <- My_Facet_Plot + scale_x_discrete(labels = function(x) lapply(strwrap(x, 
    width = 10, simplify = FALSE), paste, collapse = "\n"))

## Labels for bar charts
My_Facet_Plot <- My_Facet_Plot + geom_text(aes(label = ifelse(Sales >= 
    1, paste0("$", round(Sales, 0)), ""), y = Sales), 
    colour = "black", vjust = -0.5)

## Format Y axis
My_Facet_Plot <- My_Facet_Plot + scale_y_continuous(labels = dollar)

## Add theme for Ggplot
My_Facet_Plot <- My_Facet_Plot + theme_few(base_size = 22)

## Remove y and x axis titles
My_Facet_Plot <- My_Facet_Plot + theme(panel.grid.major = element_blank(), 
    panel.grid.minor = element_blank(), axis.title.x = element_blank(), 
    axis.title.y = element_blank(), legend.position = "none")

## Display Facet Plot
Power BI.JPG

The business case for trellis bar charts

Graphs such as these are great for exploring your data, but also have interesting applications for embedding in dashboards and presentations.

Users can discern between facets in a way not achievable when the report displays all of the data in one spot.

Also, embedding these reports in Power BI gives users the ability to update and to filter the information without reworking your R code for every permutation you would like to explore.

Enjoy the article? Want to discuss services Datalore can offer you? Click the button below and send us an email!