Power BI — Tips and Tricks
Histograms in Power BI Desktop
How to create histograms in Power BI Desktop …
Why is there (apparently) no ready-made histogram visual in Power BI Desktop? How do you create a histogram chart in Power BI? These questions keep coming up, so in this article I want to show you several ways to create histograms in Power BI.
Why charts and histograms are helpful
Our brain is amazingly good at recognizing patterns and relationships when (!) data is presented as graph or chart. Many data-driven programs and tools, such as Excel and Power BI, therefore offer intuitive and easy ways to visualize data; many programming languages even have their own libraries (e.g. Matplotlib for Python) for this purpose.
In this regard, histograms are often a good starting point for analyzing unknown data. You can quickly get a feeling whether there are clusters or typical distribution patterns in the data just by seeing a histogram.
Example of a histogram
As simple example and introduction, let’s take the age structure of the members of the German Parliament (“Bundestag”) in September 2021. If we were to create a histogram manually, we would first sort the members of parliament by age. Then we would divide them into groups/classes, here for example in 5-year groups (29–34, 35–39 years of age, etc.) and finally show the number of parliamentarians per class as a column chart — a simple histogram is born.
Without going deep into the data, this chart alone gives us a very good first impression of which age groups are strongly represented and which are less strongly represented. This can be very helpful if aggregations across the whole data (e.g. mean value) are insufficient or not meaningful.
About class societies and column charts
As shown at the beginning, histograms look similar to column charts. In other words: histograms use bars to display a frequency distribution. This also makes clear why it is not absolutely necessary to have an explicit histogram visual (although there is one).
Another very important thing to know about histograms is that the data is grouped / divided into classes (bins). It is up to the creator of the histogram to choose a meaningful bin size and it is up to the addressee to judge the meaningfulness of the bins size (lying with numbers / diagrams).
It is easy to create a histogram if the classification already exists (and makes sense), as shown in the table. In these cases, creating a histogram is just creating a column chart.
It becomes more interesting and realistic when we have the following data structure in Power Bi.
In these cases, we have a data record of first name, surname and age for each person (e.g. study participant, member of parliament). Now it is up to us to make a division into bins and to determine the frequencies.
How do we do this in Power BI Desktop?
Option 1 — Instant Visuals
Many questions about a missing histogram visual only show that the person asking the question has not tried Power BI hard and long enough. 😃 Of course there are histogram visuals. To use one of them proceed as follows.
First click the symbol with the three dots (“…”) and then click “Get more visuals”.
In the next window (picture above) search for “histogram” and select the Microsoft Histogram Chart.
Install the visual, as shown above, by clicking the button (“Get It Now”).
Once that’s done, select and use the visual as any other visual.
To do this, drag the “age”field (or any other field whose distribution you want to visualize) into the “Values” field and into the “Frequency” field.
Find the age dropdown unter “Frequency” an choose “Count”, because we want to count the people of each age group and not have summed up something. And that’s all — the histogram is ready.
Unfortunately, what bothers here is the automatic definition of the bins size. The visual created groups from 0 to 9.88 years of age and 9.89 to 19.75 years of age, etc. This is probably not useful. However, the visual allows us to change this.
Well that was easy right?
Option II — Do it yourself!
We don’t need the instant histogram visual from the previous section. We can build a histogram just with “on-board tools”. Let’s take the list of the members of the German Parliament from the very top again, as an example.
From the year of birth (unfortunately we do not have a full date of birth) we can calculate the age by using a calculated column.
Either you use one of the numerous date functions or you simply enter the current year as a fixed value. The result should look like this in any case.
Now we can use the grouping functionality of Power Bi Desktop and ask the programm to create a “class society” for us. To do this, right click on the “Age” field and create a new group.
In the following dialog we can define the size of our group (better: class or bin). In our example I’d like to group every five years of age (e.g. 29–33 old MPs). Therefore select “Size of bins” under “Bin Type” and enter 5 as “Bin Size”.
You should now see an additinal field in the data model, namely “Age (bins)”.
By using this grouped field you can now create a histogram from a simple column chart.
The result should look like this.
The age group 30–35 is now displayed as 30 here. This somewhat inadequate axis labeling is actually the only disadvantage I see in this DIY solution.
Incidentally, by formatting the X-axis, we can make our column chart look a little more like a classic histogram.
As a result, the bars move much closer together — a matter of taste. 😃
We can also adapt our chart in other ways, for example let’s extend the size of the groups/classes/bins to 10 years. To do this, we just edit our grouping settings.
Now just enter 10 (for 10 years) as the size of the bin.
It’s up to you to decide whether the result is still useful.
It is also possible that we do not specify the size of the bins, but rather specify how many bins are to be created. To do this, select the “Number of bins” option under “Bin Type” and specify the number of bins under “Bin count”. With the following settings, the result would be a histogram with exactly 6 columns; each column (based on our data) would include an age group of 8.66 years.
This also workes — as can be seen below.
Option III — Some more DIY
The grouping functionality shown above can also be used to create more individual groups. Simply choose “List” instead of “Bins” as “Group type”, and create your own groups.
However, depending on the data, this manual process can be quite tedious. From my point of view, however, it is more problematic that the data in the visual is no longer correctly sorted. At least I haven’t been able to force a proper sorting — and believe me, I’ve tried. 😫
Therefore, I’ll show another DIY variant. In this variant we’ll use an auxiliary table. It is up to you whether you import this table in any way, generate it via DAX or simply enter it manually via “Enter Data”. I decided to enter the data manually in this case.
Since I was too lazy to enter the name of the class by hand, I created it using a calculated column.
Then we can count the corresponding parliamentarians for each group/bin by using simple DAX formula.
Here, too, there are of course many other ways to achieve such a grouping using DAX. The ultimate goal is to have a group name for each bin, a sort order and a group frequency in a table. With this data we can simply create a bar column again. Seen below, however, still with a wrong sorting.
But this time we can define our own sorting order using “Sort by column”.
An the result is a histogram.
Note: I don’t know what the problem is, but the sorting doesn’t always work. Same data, same visual, same settings — sometimes it works, sometimes it doesn’t. Sometimes it helps to create an auxiliary table, create a relationship and simply drag fields into the visual again and suddenly Power BI Desktop re-sorts the visual. 😕
Option IV — Python
Finally, let’s look at a completely different approach: Python Visuals.
We can create visuals with the help of Python, Matplotlib and Pandas dataframe. I have already written a few articles on the use of Python in Power Bi — I therefore assume that Python will basically work in your Power BI environment.
The starting point of this solution is to open an empty Python visual. This is done by using the button Py button shown in the picture below.
As a result you should see (picture below) an empty visual with a Python symbol and a Python script editor.
Like any other visual, we can assign any data (fields) to a Python visual.
In this case, it would have been enough to just pass “Age”, but I wanted to show you how our script editor is automatically updated by this step and the assigned fields can be accessed in the code.
The green code snippets are always executed by Power BI in the background and cause Python to create a DataFrame. After these lines we can add our own lines to the code.
To work with visuals, we need to import the matplotlib library.
import matplotlib.pyplot as pltBy using matplotlib we can use the following code to generate a histogram. To do this, we pass the “Age-Column” of our DataFrame tho the hist method. The parameter “bins” specifies the number of bins. The remaining three parameters only determine the color of the histogram.
plt.hist(dataset.Age, bins=10, edgecolor="#6A9662",color="#DDFFDD", alpha=0.75)By the way, the above code returns the following values, so let’s refactor this line. In our case we don’t really need “n” and “edges”, but it doesn’t hurt either and it might be useful for possible future extensions .
n, bins, edges = plt.hist(dataset.Age, bins=10, edgecolor="#6A9662",color="#DDFFDD", alpha=0.75)By using “xticks” and passing “bins” to it we can set the current tick locations and labels of the x-axis so that it exactly matches the bins. The last line simply paints the histogram.
plt.xticks(bins)
plt.show()Below you can find the entire code with formatting.
If the visual does not update automatically, we can force this via the “play” button in the editor.
And the result is… a histogram 😄
Since this histogram was generated using Python code, there are countless possibilities to customize it.
Have Fun!
