In this topic, we’ll learn to create a column chart limited by a measure value as given by a user.
Before we begin, we highly recommend reading our previous articles on dynamic chart creation, and a dynamic chart sorted by measure before you get into this post.
Consider the following chart with department names on X-Axis and the count of employees in Y-Axis.
We can see that the department names are not clearly visible.
In some cases, we might have departments that have very less employees in them. Similarly, in terms of sales data, you might encounter a set of products with very less sales. Instead of having each of those columns, we can limit the number of columns to be displayed for a more visually concise and clear column chart.
That is exactly what we will create in this session! Read along.
Consider a sample data where we have a list of employees’ ID, their Names, and Departments.
What do we aim to accomplish?
This session aims to create a chart that limits the number of columns to be displayed based on an arbitrary number chosen by the user. A chart that is dynamic and looks something like this:
As a preparatory step, always convert your data into an Excel Table (we’ll call this Data), this increases formula readability and scalability when the data expands.
Let’s get into chart creation now!
First, let us extract the count of all distinct departments from our “Data” table, we use the COUNTA and UNIQUE functions for this in cell C4:
=COUNTA(UNIQUE(Data[Department])
That is, if we create a default column chart for all departments, we’ll get 9 columns. Let us look at how to limit this.
Step 01:
Find out the short list of departments that satisfy the threshold.
Consider the input threshold in cell I4.
We’ll use the LET function in cell C7 to get the shortlist. We have explained in detail, the use of the LET function in a previous article on dynamic column charts sorted by measure, please read the same.
This function allows us to assign names to parts of your formula.
=LET(
Departments, UNIQUE(Data[Department]),
Counts, COUNTIF(Data[Department], Departments),
FILTER(Departments, Counts>=I4)
)Let us understand the formula above,
- First, we need a distinct list of departments in our “Data” table using UNIQUE, and let us call them Departments
- Next, we need the count of the number of employees where we use the COUNTIF function to count the departments in our “Data” table and we check this against the distinct list of departments, which we named in step a. We’ll name this as Counts.
- After defining the two components we needed, the result we need is a FILTER of the departments only when the Counts are greater than or equal to our user-given limit measure in cell I4.
Step 02:
Now, we have to group all the columns (departments) which are not in our short list as “Others”. For this let us create a new column “New Department” in our raw data table “Data”.
a. First, we’ll use a MATCH function to look up the department from the table, and look up in the shortlist and we’ll look for an exact match.
=MATCH([@Department], $C$7#,0)This returns the position of the department in the corresponding short list, if there is no match this returns a #N/A error.
b. Now, we can wrap this formula within an ISERROR function which returns a Boolean whenever it encounters an error.
=ISERROR(MATCH([@Department], $C$7#,0))
c. We use an IF function to name the departments that have a TRUE from the previous step as “Others” since these are NOT present in our shortlist.
=IF(ISERROR(MATCH([@Department], $C$7#,0)), “Others”, [@Department])
Step 03:
Now, we’ll focus on getting the list of departments for our column chart. We’ll tweak the LET function from our Short List but, instead of the Department, we’ll use the New Department from our Data table.
And use SORTBY to sort our Departments based on the Counts.
=LET(
Departments, UNIQUE(Data[New Department]),
Counts, COUNTIF(Data[New Department], Departments),
SORTBY(Departments, Counts, -1)
)Step 04:
The count of Employees is a straightforward step, with COUNTIF as shown:
=COUNTIF(Data[New Department], D7#)Now the data we need for the chart is dynamic based on the limit of measure value:
Note: The Others column need not always be the lowest in the sort order, this is purely based on the limit measure given by the user:
Let us build our column chart limited by measure,
Since this has been extensively covered in our previous articles, I recommend you give it a reading to understand the detailed step-by-step process of creating a dynamic chart.
The overview of the steps are:
1. Select the data and create a clustered column chart
2. Define names for Department from D7 as Dept and make the range dynamic by using a #;
3. Similarly, for Employees from E7 as Emp and
4. All that’s needed now is to point to our column chart to refer to these ranges: right-click on the chart select Data, and click on the series to edit the range:
Similarly for the horizontal axis series,
Now, we have a dynamic chart that limits columns based on the measure values as chosen by the user.
We have a dedicated YouTube video explaining the steps to create a column chart limited by measure:
If you have any feedback or suggestions, please post them in the comments below.
