In this series of blog posts, we will look into 2 different ways to create a histogram (bins) in Power BI.
Let us first talk about the data that we will be using for this post. The sample data can be downloaded here.
Our sample data contains columns such as employee ID, sale amount, hours, and name of each employee.
The task that we need to accomplish is to bin the count employees by their efficiency. In other words, we need to find how many employees have X efficiency and how many employees have Y efficiencies so on and so forth.
We will be taking two approaches to achieve this. Method 1 will create a calculated column in the data table and we will use the SWITCH() function to bin the employees.
Method 2 will make use of the disconnected table and using the VALUES() DAX function we will find the corresponding employee.
Let us begin with method 1.
Click on get data and select the sample file.
- At this point, there is no need to transform the data as our data for this exercise is already clean. Once the data is loaded, click on the data/model tab and right-click on the table name and let us first rename it to Main_Data.
First of all, let us create the following measures. These are Total Sales, Total Hours, and Efficiency.
- Total Sales is the sum of all the sale amount.
- Total Hours is the sum of all the hours.
- Efficiency is Total Sales divided by Total Hours. For Efficiency measure, we will use the DIVIDE() function.
- Right-click the table (Main_Data) and click a new measure.
- Let us call it Total Sales. After the ‘=’ sign, type in SUM(Main_Data[Sale Amt]) and hit enter.
- As a good practice, immediately click the $ symbol and choose the currency format. (I have USD and I have also comma as thousand separator).
Repeat the same process for Total Hours. The DAX formula for Total Hours is Total Hours = SUM(Main_Data[Hours]).
Repeat the same process for Count_Names. The DAX formula for Count_Names is Count_Names = COUNT(Main_Data[Emp Id]).
Finally, let us create an Efficiency measure by using the following function. Efficiency = DIVIDE([Total Sales], [Total Hours],0).
Click on the Report Canvas tab and select the matrix visualization from the list of Visualizations.
In the Rows field, drag and drop the names. In the Values field, drag and drop the Efficiency.
As we can see from the visual, 11 Names are corresponding to the efficiency of 100.
Now our next task is to bin employees whose efficiency is from 0-10, 11-20, etc.
Let us go back to the data/model view and click on the new column under the modeling tab.
In the formula bar input the below formula.
BinCol = VAR mylbshr = [Efficiency] RETURN SWITCH ( TRUE (), mylbshr <= 10, "<= 10", mylbshr > 10 && mylbshr <= 20, "10 - 20", mylbshr > 20 && mylbshr <= 30, "20 - 30", mylbshr > 30 && mylbshr <= 40, "30 - 40", mylbshr > 40 && mylbshr <= 50, "40 - 50", mylbshr > 50 && mylbshr <= 60, "50 - 60", mylbshr > 60 && mylbshr <= 70, "60 - 70", mylbshr > 70 && mylbshr <= 80, "70 - 80", mylbshr > 80 && mylbshr <= 90, "80 - 90", mylbshr > 90 && mylbshr <= 100, "90 - 100", mylbshr > 100, " More than 100" )
- Let us break down this formula.
- We are declaring a variable and storing the value of the measure ([Efficiency]) in it.
- We are then returning the value based on the SWITCH() function.
- We are saying that if the efficiency is <=10 then return me “<=10” else continue.
- This function evaluates all the rows and inputs the value based on this test in the Bin_Col column.
Click the Clustered Column Chart icon in the visualizations pane. Drag and drop Bin_Col in the Axis field and Count_Names in the Values field.
We have now created histogram/binning in Power BI. In the next blog post, we will create a very similar histogram but using the Disconnected Tables method. Please do let me know what you think of this.
Till next time !!!