The expression or field containing the data to be measured.
rank
The default value of rank is 1, which corresponds to the highest value.
By specifying rank as 2, the second highest value is returned. If rank is 3, the third highest value is returned, and so on.
SetExpression
By default, the aggregation function
will aggregate over the set of possible records defined by the selection.
An alternative set of records can be defined by a set analysis
expression.
TOTAL
If the word
TOTAL occurs before the function
arguments, the calculation is made over all possible values given
the current selections, and not just those that pertain to the current dimensional value, that is, it disregards the chart dimensions.
By using TOTAL [<fld {.fld}>], where the TOTAL qualifier is followed by a list of one or more field names as a subset of the chart dimension variables, you create a subset of the total possible values.
Max(UnitSales*UnitPrice)
The value of an order is calculated by multiplying the number of units sold in (UnitSales) by the unit price.
Returns the highest value of the result of calculating all possible values of (UnitSales) * (UnitPrice).
Max(UnitSales, 2)
Returns the value for the second highest value.
Max(TOTAL UnitSales)
The TOTAL qualifier means the highest possible value is found, disregarding the chart dimensions. For a chart with Customer as dimension, the TOTAL qualifier will ensure the maximum value across the full dataset is returned, instead of the maximum UnitSales for each customer.
Max({1} TOTAL UnitSales)
The set analysis expression {1} defines the set of records to be evaluated as ALL, independent of any selection made. For example, if a specific customer is selected, it will still return the maximum UnitSales across the full dataset.
Example - Max fundamentals
Overview
A dataset contains product sales figures by month. We want to identify the maximum sales value for each product and the month when this occurred.
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a data table called Example.
Load the data and open a sheet. Create a new table and add this field as a dimension:
Customer
Create the following measures:
=Max(UnitSales), to calculate the maximum UnitSales value.
=Max(UnitSales*UnitPrice), to calculate the maximum value after multiplying UnitSales by UnitPrice on a row-by-row basis.
=Max(UnitSales, 2), to calculate the second highest value.
=Max(TOTAL UnitSales), to calculate the highest total regardless of the chart dimension.
=Max({1} TOTAL UnitSales), to calculate the highest total ignoring all selections.
Results table
Customer
Max(UnitSales)
Max(UnitSales*UnitPrice)
Max(UnitSales, 2)
Max(TOTAL UnitSales)
Max({1} TOTAL UnitSales)
Totals
10
150
9
10
10
Astrida
10
150
9
10
10
Betacab
5
50
2
10
10
Canutility
8
120
-
10
10
In this example, observe the following:
In the first measure (Max(UnitSales)), the highest value for UnitSales for the customer Astrida is 10. The Totals cell in this column returns 10 because this value is also the highest UnitSales value in the entire dataset.
In the second measure (Max(UnitSales*UnitPrice)), 150 is returned for Astrida.This is the highest value for that customer when multiplying UnitSales by UnitPrice. The Totals cell for this column also returns 150, as this is also the highest calculated value in the entire dataset.
In the third measure (Max(UnitSales, 2)), the second highest UnitSales value for the customer Astrida is 9. This value is also the second highest value of all three customers, therefore the Totals row also returns 9.
The fourth measure (Max(TOTAL UnitSales)) returns the value 10. This is the highest value found independent of the customer dimension, therefore, all rows return the same value. However, if you select the customer Betacab, the table will return only values for that customer, in this case 5 because this is the highest UnitSales across all products for Betacab.
The fifth measure (Max({1} TOTAL UnitSales)) returns the value 10 regardless of which Customer is selected because set modified {1} is used. For example, if you select Betacab this column will continue to return the value 10.
Example - Max scenario calculating highest sales by month
Overview
A dataset contains product sales figures by month. An sales manager wants to identify the maximum sales value for each product and the month when this occurred.
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a data table called Example.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
Product
Month
Create the following measures:
=If(Sales = Max(TOTAL <Product> Sales), Sales), to calculate the maximum sales for each product. In the property panel of the measure, select Add-ons > Data Handling. Clear the Include zero values checkbox to remove any null values.
Results table
Product
Month
If(Sales = Max(TOTAL <Product> Sales), Sales)
A
2024-02-01
700
B
2024-02-01
800
C
2024-03-01
710
The results of the measure return the maximum sales value for each product and the month in which this value was reached. For example, Product B had the highest sales (800) in 2024-02-01.
When you visit any website, it may store or retrieve information on your browser, mostly in the form of cookies. The information does not usually directly identify you, but it makes the site work as you expect it to and can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.
Privacy & Cookie Notice
Manage Consent Preferences
Strictly Necessary Cookies
Always Active
These cookies are necessary for the website to function and cannot be switched off in our systems. They are usually only set in response to actions made by you which amount to a request for services, such as setting your privacy preferences, logging in or filling in forms. You can set your browser to block or alert you about these cookies, but some parts of the site will not then work.
Functional Cookies
These cookies enable the website to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies, then some or all of these services may not function properly. These cookies do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device.
Performance Cookies
These cookies allow us to count visits and traffic sources so we can measure and improve the performance of our site and make it easier to navigate. For example, they help us to know which pages are the most and least popular and see how visitors move around the site. When analyzing this data it is typically done on an aggregated (anonymous) basis.
Advertising Cookies
These cookies may be set through our site by our advertising partners to build a profile of your interests and show you relevant advertisements on other sites. They do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device. If you do not allow these cookies, you will experience less relevant advertising.