Histogram in Power BI

Introduction

They say there is no visualisation for a histogram in Power BI. That's true. The implication of that statement is, therefore, that it is not possible to create a histogram in Power BI. Well, that's not true so let's see how to create a histogram in Power BI.

Groups

As you know, I don't like to talk about tricks in software: you know, when someone says that these are his top ten tricks in Excel. There are very few tricks in software: they are features. So, what I am about to describe is a feature of Power BI that we can usefully use to create a histogram: Groups.

Imagine we have the following data and we have created a Power BI file to store and use it all.

It's a simple list of monthly salaries, by employee. There are 150 employees altogether. We want to see the following histogram from the data:

This is how I did that.

Open the Query Editor with your query, like this: click on the Salaries column heading to select that column, then

This opens a new column for us to work with: in this case, create the groupings that we need for our histogram's bin range.

I just typed 4200 to 4900 ... this is crucial, setting the bin ranges. I already know that my data ranges from around 2,300 to 4,900 and I did not want huge bin ranges and I did not want to include in my ranges values that do not exist. That is, I wanted 6 or 7 bars in my histogram and to achieve that, I chose bin ranges of 2100 to 2800, 2800 to 3500 ... all the way to 4900 to 5600.

Having entered 4200 to 4900 in that first row, I press Enter and ... by using Flash Fill, Power BI automatically completes the column. It has worked out what we wanted to do from that one entry and that saves us a lot of effort. By the way, check the answers! Sometimes, Power BI doesn't get it right so we would need to correct it: not difficult but it needs to be done.

Click OK and edit the column heading of your new column and you are ready to create your histogram.

All that is left is to sort the bin ranges on the horizontal axis:

And there you are, your histogram: it takes longer to describe than it does to prepare. Download my PBIX file from the link below and try it for yourself.

Summary

With the advent of Flash Fill and Grouping in Power BI, we have been able to create histograms like the one you see here. There is another way to create groups and therefore create a histogram in Power BI, using the New Group feature. However, I believe the technique I have described here is superior to the New Group method because this way, we can more easily take control of our bin range sizes. Note, I said more easily.

In my PBIX file, I describe how to create a new group and therefore how to create the second example of the histogram ... bonus material!

Power BI file: download

Duncan Williamson

27th June 2021