In this blog we are going to talk about the 3 different types of calculations in tableau what they are and how you can go about using them. So what are the 3 types of calculations:
1: Basic Calculations - These can be split into 2 types row level calculations and Aggregate level calculations. As the name suggests row level calculations work on every row of your data set while aggregate calculations takes the aggregate of whatever fields your using within your calculation.
2: Level of Detail Calculations - Level of detail (LOD) calculations allow you to perform calculations at different levels of granularity in your data. For example, you can calculate the average salary for each department, or the sum of sales for each product category. LOD calculations can be created using the "Level of Detail" menu, which can be accessed by clicking on the "Analysis" tab in the top menu.
3: Table Calculations - Table calculations are calculations that are performed across the entire data table, rather than on individual measures or dimensions. These calculations can be used to create running totals, percentiles, and other advanced calculations. Table calculations can be created using the "Table Calculation" menu, which can be accessed by clicking on the "Analysis" tab in the top menu.
So how do you use these calculations in tableau:
To use most of these calculations in Tableau, you first need to create a calculated field. To do this, click on the "Analysis" tab in the top menu and then select "Create Calculated Field". This will open a new window where you can enter the different functions that are available to use.
Most complex calculations in tableau will require the use of the different functions available to use in tableau these are some of the basic ones you will end up using:
Logical Functions:
IF [expression] THEN <then> ELSE <else> END - if the dimension your checking falls in line with your <then> statement then your statement returns true otherwise it returns whatever your <else> statement is. Can also add ELSEIF if you want to test a second expression.
AND - both expressions must be true
OR - only 1 expression must be true
IIF ([exp], <then>, <else>) - is basically an if statement without having to write down the full statement.
CASE [exp]
WHEN <value 1> then <return1>
WHEN <value2> then <return2> ELSE <Defualt return> END - easier to test a list of expressions.
2: Level of Detail Calculations
FIXED - independent of the view and only looks and the expression for level of granularity.
EXCLUDE - minus from the view
INCLUDE - adds to the view
syntax is { TYPE OF LOD [dimension list] : AGGREGATE }
3: Table calculations
There are several key concepts to understand when working with table calculations in Tableau:
- Direction - the direction at which this calculation is carried out e.g. across or down a table
- Scope - are you restarting the calculation for each group e.g. for the entire table, the pane or just the cell.
- Layout - if the numbers are moved around on the table the numbers collected will be different
- Filters - if numbers are excluded then the results will again be different as those numbers are no longer present in the table.
To create a table calculation in Tableau, you can use the quick table calculation option or the full table calculation dialog. The quick table calculation option is accessed by right-clicking on a measure and selecting "Quick Table Calculation." This will bring up a menu of common table calculations, such as running total or percent of total, which you can select and apply to the measure.
The full table calculation dialog can be accessed by right-clicking on a measure and selecting "Edit Table Calculation." This will bring up a more detailed dialog where you can specify the calculation type, partitioning, addressing, and direction.
This can be read simply as:
