NOTE: All the example Questions in this page use this Example Dataset.
Functions are usually composed of the Function's name, parentheses and one or more Arguments separated by commas between the parentheses. Additionally, there can be Grouping or a Filter inside.
Some Functions have a fixed number of Arguments. For example, the
RATIO Function always takes two arguments - a dividend and a divisor.
Some Functions have variable number of Arguments. These functions can behave differently depending on the number of Arguments used.
SUM(SALES)adds the Sales value from all the rows in the Data.
SUM(SALES, PROFIT)adds the Sales and Profit values in each row. You'll usually wrap it in a Function like
AVERAGEto get the final Measure.
Filters in Functions
You can set up a Filter inside a Function. It will filter the Data on which the computation will be performed.
WHERE SALES > 0
)adds the Sales value from only those rows where Sales is more than 0.
WHERE PROFIT > SALES/10
)adds the Sales value from only those rows where the Profit is more than 1/10th of Sales.
Grouping in Functions
You can do grouping within Functions using the
ACROSS Keyword. Take the following example:
First, this groups the Data by each City. Then, for each of these groups, it adds the Sales values. Finally, it computes the average value of these totals.