Data Aggregation in Tableau

In Tableau, the aggregate function allows you to summarize your data, an aggregation performs a calculation on the set/group of values and returns the single value.

For example, you might want to know exactly how many orders your company had for a particular year. You can use the COUNTD function to summarize the exact number of orders your company had, and then break the visualization down by year.

You can aggregate either Measures or Dimensions in Tableau, but most commonly we used to aggregate Measures.

Whenever you add a measure variable to the rows or columns, the aggregation has been applied to it by default. The type of aggregation applied varies depending on the context of the view.

Tableau Has some of the Pre-defined functions which are explained below with an example

Introduction to Tableau

ATTR() Function in Tableau

  • This function returns the value of the expression if it has a single value for all rows. Otherwise returns an asterisk(*). Null values are ignored.
  • The syntax for ATTR() is as below
    ATTR(expression) 
    //** ATTR=attribute 
    example: ATTR(City), ATTR(ORDER_ID)*//
    
The following example demonstrates the Attribute function:
  • Open Tableau and click on Connect to Data.
    connect-to-data-tableau
  • Then the Connect page will open, click on Microsoft Excel.
    click-on-connect-to-data-excel-file-tableau
  • Navigate to the downloaded files, select Sample Superstore excel file, which is a saved excel file, and click on Open.
    connect-sample-superstore-to-tableau
  • You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
  • Once the data source has been connected, open a new worksheet by clicking on sheet1.
    naviagte-to-sheet1-tableau
  • Let us create a crosstab by dragging Order_ID and Product Name variables to the row shelf as below.
    create-view-with-order-id-and-product-name-tableau
  • In the above image, you can see that each product has orders from a different order Id.
  • You can find out the single ordered products and multiple ordered products by making the Order ID into an attribute.
  • Right-click on the Order Id and select Attribute.
    right-click-on-order-id-and-select-attribute
  • The Order ID has been converted to attribute, you can see in the below graph which shows an asterisk(*) for the product name which has multiple orders and displays the Order Id for the product which has single order.
    differentiating-single-and-multiple-orders-tableau

This is how the ATTR() function helps us to find single and multiple orders for the product.

You can also apply the filter by right-clicking on the Order ID and select Show Filter.
right-click-order-id-select-show-filter-tableau

Now you can see a Filter table ATTR(Order ID) in the left corner, by doing uncheck All and Asterisk(*) field you can filter the multiple orders and can see only the single order product in the view as below.
sigle-order-list-tableau

And if you select the Asterisk(*) option, you can see the product name with multiple orders.
graph-with-multiple-order-tableau

Tableau Dashboards

AVG() Function in Tableau

  • This function returns the average of all the values in the expression. AVG can be used with numeric fields only. Null values are ignored.
  • The Syntax for AVG() function is as below:
    AVG(expression)​ //*AVG=Average value, AVG(Profit)*//
The following example demonstrates the AVG():
  • Open Tableau and click on Connect to Data.
    connect-to-data-tableau
  • Then the Connect page will open, click on Microsoft Excel.
    click-on-connect-to-data-excel-file-tableau
  • Navigate to the downloaded files, select Sample Superstore excel file, which is a saved excel file, and click on Open.
    connect-sample-superstore-to-tableau
  • You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
  • Once the data source has been connected, open a new worksheet by clicking on sheet1.
    naviagte-to-sheet1-tableau
  • We are going to find the average of sales in accordance with the order date.
  • Let us create a view by dragging the Order date variable to the column shelf and the Sales variable to the row shelf.
    craete-view-with-orderdate-and-sales-tableau
  • Right-click on Analysis and select the Create Calculated Field option.
    select-create-calculated-field-for-sales-tableau
  • Once the calculation opens, add the formula to find the average of sales as AVG([Sales]) and name the calculation window as AVG. Sales and click on OK as below.
    average-of-sales-formula-tableau
  • The new field called AVG(Sales) has been created under the measure, drag AVG(Sales) to the row shelf, you will get the below graph which shows the average sales.
    graph-with-avg-sales-tableau
  • You can add AGG(AVG.Sales) to the color tab and label on the Marks shelf, then you can see a higher average with a darker shade and sales details in the label.
    avg-sales-with-labels-and-colors-tableau

Tableau Data Relationship

SUM() Function in Tableau

  • This function returns the sum of all values in the given expression. SUM can be used with numeric fields only. Null values are ignored.
  • The syntax for the SUM() function is as below.
    SUM(Expression) 
The following example demonstrates the SUM() function:
  • Repeat the steps from AVG() function from starting till opening the Create Calculated Field.
  • Once the create calculation field has opened, enter the name as SUM. Sales and enter the formula to find the sum of sales as SUM([Sales]).
    calculation-field-with-formula-sum-of-sales-tableau
  • The SUM(Sales) field has been created under the measure, drag SUM(Sales) to the row shelf, you will see the below graph.
    graph-with-sum-of-sales-and-order-date-tableau
  • If you drag AGG(SUM.Sales)) to the color shelf and label shelf, you can clearly differentiate between the sales and SUM sales as below.
    graph-with-sum-of-sales-with-color-and-label-tableau

Data Sorting in Tableau

MIN() Function in Tableau

  • This function returns the minimum of an expression across all records. If the expression is a string value, this function returns the first value where the first is defined by alphabetical order.
  • The syntax for MIN() function is as below:
    MIN(Expression1, Expression2)
The following example demonstrates the MIN() function:
  • Repeat the steps from AVG() Function from starting till opening the Create Calculated Field.
  • Once the create calculation field has opened, enter the name as MIN. Sales and enter the formula to find the sum of sales as MIN([AVG.Sales], [SUM.Sales]).
    calculated-field-with-min-function-tableau
  • Now, the MIN. Sales field has been created under the Measure, Drag MIN. Sales to the row shelf, you will find the graph which shows the MIN(Sales) values out of AVG(Sales) and SUM(Sales) value.
  • Dragging the AGG(MIN.Ssales) field to the Colors and Labels tab in the Marks shelf helps you to highlight the shades.
    graph-which-diplays-min-avg-sum-of-sales-tableau

Formatting in Tableau

MAX() Function in Tableau

  • This function returns the maximum of an expression across all records. If the expression is a string value, this function returns the last value where last is defined by alphabetical order.
  • The syntax for MAX() function is as below:
    MAX(Expression1, Expression2)
The following example demonstrates the MAX() function:
  • Repeat the steps from AVG() Function from starting till opening the Create Calculated Field.
  • Once the Calculated field has opened, name the Filed as MAX. Sales and enter the formula to find out the maximum sales value by comparing AVG(Sales) and SUM(Sales).
    create-calculated-field-to-find-max-sales-tableau
  • Now, a new field named MAX. Sales have been created under measure, drag MAX. Sales field to row shelf.
  • Next, drag MAX. Sales to the color tab and label tab in the Marks shelf, then you will find the graph as below, which shows the Maximum sales value by comparing AVG(Sales) and SUM(Sales).
    find-max-sales-from-avg-sales-and-sum-sales-tableau

Boxplot chart in Tableau

VAR() Function in Tableau

  • This function returns the statistical variance of all values in the given expression based on the given sample.
  • The syntax for VAR() function is as below.
    VAR(Expression)
The following example demonstrates the VAR() function:
  • Let us find the variance of profit in accordance with Order Date in a yearly wise and quarterly wise.
  • Open Tableau and click on Connect to Data.
    connect-to-data-tableau
  • Then the Connect page will open, click on Microsoft Excel.
    click-on-connect-to-data-excel-file-tableau
  • Navigate to the downloaded files, select Sample Superstore excel file, which is a saved excel file, and click on Open.
    connect-sample-superstore-to-tableau
  • You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
  • Once the data source has been connected, open a new worksheet by clicking on sheet1.
    naviagte-to-sheet1-tableau
  • Create a view by dragging the Order date to the column shelf and Profit to the row shelf.
  • Click on the Analysis tab and select Create Calculated Field.
    select-create-calculated-field-for-sales-tableau
  • Name the field as VAR.Profit and enter the formula as VAR([Profit]) as below.
    create-calculation-field-var-profit-tableau
  • Now, the new field named VAR. Profit has been created under measure, drag VAR. Profit to the row shelf.
  • Drag VAR.Profit to the color tab and label tab in the Marks shelf.
  • Click on the + symbol in the Year(Order Date) to convert it to Quarter(Order Date).
  • Now, you will get a graph which shows the variance of profit in a quarter wise as below.
    var-function-shows-profit-tableau

Charts in Tableau

VARP() Function in Tableau

  • This function returns the statistical variance of all values in the given expression on the entire given sample.
  • The syntax for VAPR() function is as below:
    VARP(Expression)
The following example demonstrates the VARP() function:
  • In this example, we are going to find the complete variance of the Profit over the Order Date.
  • Repeat the steps from VAR() Function and from the starting till open Create Calculated Field.
  • Once the Create Calculated Field window has been opened, Name the field name as VARP. Profit and enter the formula as VARP([Profit]).
    formula-to-find-varp-profit-tableau
  • Now, a new field has been created with the name VARP. Profit under the measure, drag this VARP.Profit to the row column.
  • And drag VARP.Profit to the color tab and label tab in Marks shelf.
  • The below graph will be displayed which shows the complete variance of the profit Order Date as shown below.
    graph-with-varp-of-profit-tableau

Line Chart in Tableau

STDEV() Function in Tableau

  • This function returns the statistical standard deviation of all values in the given expression based on a given sample.
  • The syntax for STDEV() function is as below:
    STDEV(Expression)
The following example demonstrates the STDEV() function:
  • In this example, we are going to find the standard deviation of sales in the given data source over Order Date.
  • Repeat the steps from AVG() Function from starting till opening the Create Calculated Field.
  • Once the create calculation field has opened, enter the field name as STD. Sales and enter the formula to find the standard deviation of Sales as STDEV([Sales]).
  • Now, the new field named STD. Sales have been created under measure, drag, STD. Sales to the row shelf.
  • Drag STD.Sales to the color tab and label tab to the Marks shelf to highlight the values.
    std-of-sales-tableau

Tableau Dashboards

STDEVP() Function in Tableau

  • This function returns the statistical standard deviation of all values in the given expression based on a biased population.
  • The syntax for the STDEVP() function is as below:
    STDEVP(Expression)
The following example demonstrates the STDEV() function:
  • In this example, we are going to find the standard deviation of the whole sample from the Sales field.
  • Repeat the steps from the STDEV() Function from starting till open Create Calculated Field.
  • Name the calculation field as STDP. Sales, enter the formula as.
    field-name-stdp-sales-with-formula-tableau
  • Now, the new field called STDP. Sales filed has been created under the measure, drag STDP.Sales to the row shelf.
  • Next, drag STDP.Sales to the color and label tab in the Marks shelf then the below graph will appear. Which shows the overall standard deviation of sales over the order date.
    graph-with-stdp-function-tableau

Waterfall Chart in Tableau

COUNT() Function in Tableau

  • This function returns the number of items in a group. Null values are not counted.
  • The syntax for the COUNT() function is as below.
    COUNT(Expression)
The following example demonstrates the COUNT() function:
  • In this example, we are going to find the count of Profit for State wise.
  • Open Tableau and click on Connect to Data.
    connect-to-data-tableau
  • Then the Connect page will open, click on Microsoft Excel.
    click-on-connect-to-data-excel-file-tableau
  • Navigate to the downloaded files, select Sample Superstore excel file, which is a saved excel file, and click on Open.
    connect-sample-superstore-to-tableau
  • You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
  • Once the data source has been connected, open a new worksheet by clicking on sheet1.
    naviagte-to-sheet1-tableau
  • Create a view by dragging the State to the column shelf and Profit to the row shelf.
    create-view-for-profit-and-sate-tableau
  • Click on Analysis and select Create Calculated Field.
    select-create-calculated-field-for-sales-tableau
  • Once the field has been created, name the field as COUNT. Profit and enter the formula to find out the count of profit as COUNT([Profit]).
    count-profit-field-with-formula-tableau
  • The new field COUNT. Profit has been created under the measure, drag this field to row shelf and to the color and label tab in the Marks field.
  • The below graph will appear which shows the count of the profit state wise.
    count-of-profit-state-vise-tableau

Field Operations in Tableau

COUNTD() Function in Tableau

  • This function returns the number of distinct items in a group. Null values are not counted.
  • The syntax for COUNTD() function is as below.
    COUNTD(Expression)
The following example demonstrates the COUNT() function:
  • In this example, we are going to find the distinct number of Product IDs state wise.
  • Open Tableau and click on Connect to Data.
    connect-to-data-tableau
  • Then the Connect page will open, click on Microsoft Excel.
    click-on-connect-to-data-excel-file-tableau
  • Navigate to the downloaded files, select Sample Superstore excel file, which is a saved excel file, and click on Open.
    connect-sample-superstore-to-tableau
  • You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
  • Once the data source has been connected, open a new worksheet by clicking on sheet1.
    naviagte-to-sheet1-tableau
  • Drag the dimension variable Product ID to the row shelf and another dimension variable State to the Column shelf.
  • Click on Analysis and select the Create Calculated Field.
    select-create-calculated-field-tableau
  • Name the field as COUNTD.Product ID and enter the formula to find the unique number of product ID as COUNTD([PRodcuct ID])
    formula-for-unique-product-id-tableau
  • The new field COUNTD.Product ID has been created, drag this field to row shelf and to color tab and label tab in the Marks shelf.
  • The below will be obtained which shows the count of distinct Product Id in State wise.
    graph-which-shows-the-unique-product-id-state-vise-tableau
Below Shows the List of Rarely Used Tableau Functions:
Function Syntax Definition
COLLECT COLLECT (spatial)
  • An aggregate calculation that combines the values in the argument field. Null values are ignored.
CORR CORR(expression 1, expression2)
  • Returns the Pearson correlation coefficient of two expressions.
COVAR COVAR(expression 1, expression2)
  • Returns the sample covariance of two expressions.
COVARP COVARP(expression 1, expression2)
  • Returns the population covariance of two expressions.
MEDIAN MEDIAN(expression)
  • Returns the median of an expression across all records. Median can only be used with numeric fields. Null values are ignored. This function is not available for workbooks created before Tableau Desktop 8.2 or that use legacy connections.
PERCENTILE PERCENTILE(expression, number)
  • Returns the percentile value from the given expression corresponding to the specified number. The number must be between 0 and 1 (inclusive)—for example, 0.66, and must be a numeric constant.

Tableau Story Line

Creating an Aggregate Calculation Field in Tableau

  • Open Tableau and click on Connect to Data.
    connect-to-data-tableau
  • Then the Connect page will open, click on Microsoft Excel.
    click-on-connect-to-data-excel-file-tableau
  • Navigate to the downloaded files, select Sample Superstore excel file, which is a saved excel file, and click on Open.
    connect-sample-superstore-to-tableau
  • You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
  • Once the data source has been connected, open a new worksheet by clicking on sheet1.
    naviagte-to-sheet1-tableau
  • Create a view by dragging the Category and YEAR(Order Date) to the column and row shelf respectively as below.
    drag-order-date-and-category-tableau
  • Now, right-click on Analysis and select the Create a Calculated field.
    click-analysis-select-create-calculated-field-tableau
  • You will be navigated to the calculation window as shown below, enter the formula to calculate the sum of sales.
    IIF(SUM([Sales]) !=0, SUM([Profit])/SUM([Sales]), 0)​
You can use the function reference to find and add aggregate functions and other functions (like the logical IIF function in this example) to the calculation formula.
  • And, name the calculation field as Calculation and then click on Ok.
    enter-formula-callation-window-tableau
  • Once you click on Ok in the Calculation window, you will find the new field Calculation appears under Measures in the Data pane. Just like your other fields, you can use it in one or more visualizations.
    calculation-as-measure-variable-tableau
  • When Calculation is placed on a shelf or card in the worksheet, its name is changed to AGG(Calculation).
  • And, the below graph will appear which shows the aggregation calculation using the measure variable Sum(Sales).
    aggregation-calculation-graph-output-tableau

Tableau Forecasting

Rules for Aggregate Calculations in Tableau

  • For any aggregate calculation, you cannot combine an aggregated value and a disaggregated value.
    For example, SUM(Price)*[Items] is not a valid expression because SUM(Price) is aggregated and Items is not. However, SUM(Price*Items) and SUM(Price)*SUM(Items) are both valid.
  • Constant terms in an expression act as aggregated or disaggregated values as appropriate.
    For example SUM(Price*7) and SUM(Price)*7 are both valid expressions.
  • All of the functions can be evaluated on aggregated values.
  • However, the arguments to any given function must either all be aggregated or all disaggregated.
    For example, MAX(SUM(Sales), Profit) is not a valid expression because Sales is aggregated and Profit is not. However, MAX(SUM(Sales), SUM(Profit)) is a valid expression.
  • The result of an aggregate calculation is always a measure.

Tableau Time Series Analysis

Aggregate Measure in Tableau

When you add any measure variable to the rows column, Tableau automatically aggregates its value. Average, Sum, and Median are the common functions used for measure variables.

Sales become SUM (Sales), and every measure has a default aggregation, which is set by Tableau when you connect to a data source. You can change or view the default aggregation for measures.

    • You can aggregate a measure using Tableau only for relational data sources.
    • Multidimensional data sources contain data sources that are already aggregated.
    • In Tableau, the multidimensional data source is supported only in windows.

    Set Default Aggregation for Measures:

    You can set the default aggregations for any measure variables. A default aggregation is the preferred calculation for summarizing a discrete or continuous field. The default aggregation is used when you drag a measure to a view automatically.

    • Open Tableau and click on Connect to Data.
      connect-to-data-tableau
    • Then the Connect page will open, click on Microsoft Excel.
      click-on-connect-to-data-excel-file-tableau
    • Navigate to the downloaded files, select Sample Superstore excel file, which is a saved excel file, and click on Open.
      connect-sample-superstore-to-tableau
    • You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
    • Once the data source has been connected, open a new worksheet by clicking on sheet1.
      naviagte-to-sheet1-tableau
    • Right-click on any of the measure variables in the Data field and select Default Properties then select Aggregation and then select one of the aggregation options as below.
      set-dafult-aggregation-for-measure-variable-tableau
    • You cannot set default aggregation for the published data source.
    • The default aggregation is set only when the data source is initially published.
    • Default set aggregation of measure value will look as below.
      deafult-aggregation-tableau
    When you are ready to make a data source available to other users, you can publish it to Tableau Server or Tableau Online.

    Disaggregation of measures in Tableau:

    • You can disaggregate the measured value, which means, the Tableau will display a separate pane for every data value in every row of your data source.
    • Open Tableau and click on Connect to Data.
      connect-to-data-tableau
    • Then the Connect page will open, click on Microsoft Excel.
      click-on-connect-to-data-excel-file-tableau
    • Navigate to the downloaded files, select Sample Superstore excel file, which is a saved excel file, and click on Open.
      connect-sample-superstore-to-tableau
    • You can download a Sample Superstore.xls file from the link: https://chercher.tech/files/
    • Once the data source has been connected, open a new worksheet by clicking on sheet1.
      naviagte-to-sheet1-tableau
    • Create a view by dragging the Category and Sales to the Column Shelf and Order date to the Row shelf.
      create-view-for-disaggregation-tableau
    • Next, click on Analysis and select Aggregate Measures
      select-aggregate-measures-under-analysis-tableau
    • After selecting Aggregate Measure, you will see the graph below, which shows you that, Tableau collects individual row values from your data source into a single value that is adjusted to the level of detail in the graph as below.
      disaggregate-views-of-measure-values-tableau

    Aggregating Dimensions in Tableau

    You can aggregate any dimension variable in the view as Maximum, Minimum, Count, and Count Distinct. When you aggregate a dimension, you have to create a new temporary measure column, so the dimension takes on the characteristics of a measure.

    • Consider the below view.
      create-view-for-disaggregation-tableau
    • Now, right-click on the Category, under measure you will find the options to aggregate the dimension variables as shown below.
      options-for-aggregating-measure-variables-tableau
    • If you select Minimum the below graph will appear, which shows the Minimum sales of the category(Furniture) as below.
      graph-with-min-category-measure-tableau
    • If you select Maximum, the below graph will appear, which shows the Maximum sales of the Category(Technology as shown below.
      maximum-category-with-measure-tableau
    • If you select Count, the below graph will appear which shows the count of category.
      graph-with-count-of-category-tableau
    • If you select Count(Distinct), then the below graph will appear which shows the Distinct count of Category in the given year wise is 3.

    graph-with-count-of-distict-of-category-tableau

    0 results
    Comment / Suggestion Section
    Point our Mistakes and Post Your Suggestions