Data Analysis in Tableau: Unleash the Power of COUNTIF

Data Analysts, scientists, statisticians, and others can use Tableau to show data and make clear findings from Data Analysis. Tableau is well-known for its ability to swiftly handle massive amounts of data and generate the required Data Visualization results. COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text.

In this article, you will learn how to implement the Tableau Count If Function to calculate the count of dimension members that meet a condition.

Table of Contents

• What is Tableau?
  • Uses of Tableau
  • Key Features of Tableau
• What is the COUNT IF Function?
• How to use Tableau COUNT IF Function?
  • Tableau COUNT IF Example 1: Using a Range of Values with Parameters
  • Tableau COUNT IF Example 2: Using a Text Value
  • Tableau COUNT IF Example 3: Creating Categories
  • Tableau COUNT IF Example 4: Using Table Functions
• Conclusion

What is Tableau?

In the Business Intelligence industry, Tableau is a strong and rapidly developing Data Visualization application. It aids in the reduction of raw data to a format that is simple to comprehend. Tableau assists in the creation of data that experts at all levels of a business can understand. Non-technical individuals can also make their own dashboards. With Tableau, Data Analysis is quick, and visuals in the form of dashboards and workbooks are created.

Humans understand things that are visually well descriptive and entertaining, therefore Data Visualization is vital. Working with Data Visualization tools like Tableau can help anyone better comprehend data since they provide access to a large quantity of information in easily digestible images. Furthermore, well-designed graphics are often the easiest and most effective way to deliver any facts.

Uses of Tableau

• Business Intelligence
• Data Visualization
• Data Collaboration
• Data Blending
• Real-time Data Analysis
• Query Translation into Visualization
• To Import Large Size of Data
• To Create No-code Data Queries
• To Manage Large Size Metadata

Key Features of Tableau

• Usability: Tableau is simple to use and does not necessitate any technical or programming expertise. It responds rapidly when it comes to creating a dashboard. Tableau is a Data Visualization software that can be downloaded to mobile devices and desktop computers, making it simple to access and analyze data. Real-time Data Exploration and multilingual data representation are supported.
• Connection and Sharing: Tableau includes a number of advanced capabilities, such as Data Dissemination and collaboration.
• Security: Multiple data sources are linked together in an extremely safe manner. It’s simple to import and export large amounts of data.
• Advanced Visualization: Tableau allows you to generate a variety of visuals, ranging from simple Pie Charts and Bar Graphs to more complex Histograms and Gantt Charts.

What is the COUNT IF Function?

The COUNTIF function is used to count the number of cells that match a single condition. Dates, numbers, and text can all be included. It matches the condition using logical operators such as <(Less Than), >(Greater Than), >=(Greater Than or Equal to), <=(Less Than or Equal to), =(Equals to), and <>(NOT). For partial matching, it also employs wildcards such as * and ?.

• Syntax in Excel: COUNTIF ( range, criteria)
• Arguments:
  • Range: The range of cells for which you want the cell count for a given circumstance is referred to as range.
  • Criteria: The condition for which you desire the cell count is referred to as criteria.
• Return Value: In Excel, the COUNTIF function produces an integer value representing the number of cells that satisfy the provided condition.

How to use Tableau COUNT IF Function?

Since there’s no direct Tableau COUNT IF Function, you must use nested IF conditions to get the desired output.

The general syntax is:

{ FIXED [Dimension] : SUM(
IF <Condition>
THEN 1
ELSE 0
END
) }

Here are a few examples of how to count dimension members that satisfy a certain condition using Tableau COUNT IF Function:

Tableau COUNT IF Example 1:  Using a Range of Values with Parameters

The purpose of this example is to count the number of customers with sales between two parameter values for every combination of category and segment, and the view contains [Category] and [Segment] on Rows.

• Step 1: The first step in using Tableau COUNT IF Function is to go to Analysis > Create a field that is calculated.
• Step 2: Do the following in the Calculated Field dialog box that appears, then click OK:
  • Give the calculated field a name. The calculated field, in this case, is called “# of Customers with Select Sales“.
  • Create a computation that looks like this in the formula field:

IF { INCLUDE [Customer Name] : SUM([Sales])} >= [Sales Lower Limit]
AND { INCLUDE [Customer Name] : SUM([Sales])} <= [Sales Upper Limit]
THEN 1
ELSE 0
END

• Step 3: The last step in using Tableau COUNT IF Function is to drag [# of Customers with Select Sales] to the Marks card’s Text field.

Some key considerations regarding this example:

• Because [Customer Name] is not in the view, the INCLUDE expression is required.
• SUM([Sales]) is summed up to the viewer’s Level of Detail, which in this case is [Category] and [Segment], plus [Customer Name] because the INCLUDE expression lists [Customer Name].
• If the sales per customer are below [Sales Upper Limit] and above [Sales Upper Limit], the [“# of Customers with Select Sales] computation will determine if they are below [Sales Upper Limit]. If the sales per customer are within the stated range, this computation will yield 1, which when added together will give you a total number of customers.
• A COUNTD can also be used to write this calculation ().

Tableau COUNT IF Example 2: Using a Text Value

The purpose of this example is to count orders that contain the product “Staple Holder,” and the view contains [Order ID] and [Product Name] on rows using tableau COUNT IF Function.

• Step 1: The first step in using Tableau COUNT IF Function is to create a calculated field with the name “# of Orders with a Staple Holder” and a calculation that looks like this:

{ FIXED [Order ID] : MAX(
IF [Product Name] = "Staple holder"
THEN 1
ELSE 0
END)}	

• Step 2: Text on the Marks card: [# of Orders with a Staple Holder].

Key considerations regarding this example are as follows:

• The IF statement goes through each record in the underlying data collection to see if [Product Name] equals “Staple Holder.” If this is the case, the calculation produces a value of 1 for that entry. The FIXED expression will then return the highest number of 1s and 0s for each record with the same [Order ID] value.
• Because there is only one record for each unique combination of [Product Name] and [Order ID] in Superstore, MAX() may be replaced with SUM() and the result would be the same because summing up one value is the same as taking the maximum of one value.

Tableau COUNT IF Example 3: Creating Categories

[State] has been added to Text in this example, and the purpose is to categorize states based on the number of cities having a negative profit.

• Step 1: The first step in using Tableau COUNT IF Function is to create a calculated field with a name like “Number of Unprofitable Cities” and a calculation like this:

{ FIXED [State] : SUM(
IF { INCLUDE [City] : SUM([Profit]) } < 0
THEN 1
ELSE 0
END
) }

• Step 2: The next step in using Tableau COUNT IF Function is to create a calculated field with the name “Categories based on city profit” with the following calculation:

IF [# of Unprofitable Cities] = 0
THEN "All cities have positive profit"
ELSEIF [# of Unprofitable Cities] = 1
THEN "One city has negative profit"
ELSE "Multiple cities have negative profit"
END

• Step 3: [Categories based on city profit] should be dragged to the Columns shelf.

Key considerations regarding this example are as follows:

• Because [State] is in the FIXED expression, the INCLUDE expression will yield the sum of profit per city per state. The formula will then yield a 1 for each unprofitable city, allowing the cities to be counted.

Tableau COUNT IF Example 4: Using Table Functions

The view is already filtered to show the top 10 clients by sales per category per segment in this example. The purpose is to highlight any categories or segments with more than two consumers that are losing money.

• Step 1: The first step in using Tableau COUNT IF Function is to create a calculated field with the name “3+ Top 10 Customers with Neg Profit” and a calculation like this:

IF
WINDOW_SUM(
IF SUM([Profit]) < 0
AND [Sales Rank] <= 10
THEN 1
ELSE 0
END ) > 2
THEN "Three or more customers with negative profit"
ELSE "Good standing"
END

• Step 2: Color on the Marks card with [3+ Top 10 Customers with Neg Profit].
• Step 3: Right-click [3+ Top 10 Customers w/ Neg Profit] on Color and select Compute Using > Customer Name.

Key considerations regarding this example are as follows:

• Because table calculation filters do not filter the underlying data, we must add a condition for rank inside the calculation instead of using Level of Detail (LOD) expressions in this example. RANK(), on the other hand, is a table function that cannot be utilized within a LOD computation.
• Every non-aggregated dimension in the view has an impact on table calculations. It’s possible that the table calculation isn’t delivering the right results because of the way the dimensions in the view compute the table calculation. Change the calculation using the option to fix the problem.

Conclusion

You have successfully learned about tableau COUNT IF, its uses and examples, and how to implement it using nested If statements.

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, Marketing Platforms to Tableau can seem to be quite challenging. If you are from a non-technical background or are new in the game of data warehouse and analytics, Hevo Data can help!

Visit our Website to Explore Hevo

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 100+ multiple sources to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!