Calculation expressions

In the previous section of this chapter, we created some master measures by writing calculation expressions for each one. Expressions combine functions, fields, and math operators to calculate a desired value. These can be applied not only to measures, but also to titles, subtitles, footnotes, labels, and descriptions on Master items, chart colors, dimension limits, calculation conditions, and various other tools and places.

Here are some guidelines for writing expressions, as follows:

An expression used on a measure will always have an aggregation function. The most common aggregation functions are sum(), max(), min(), and avg(). Aggregation functions require at least a field name as a parameter. Let's look at some examples:
- The sum(SalesAmount) calculates the summary of the SalesAmount field.
- The count (distinct OrderID) counts the distinct values found in the OrderID field. The distinct clause removes duplicate values found in the field, counting only the first occurrence.
The num (sum (Discount*SalesAmount)/sum (SalesAmount), '0.00%') is more complex in nature, as it calculates the discount percentage weighted by SalesAmount. The resulting calculation is formatted by num(calculation, format), which is more commonly known as the num function. The first parameter is the calculation expression, and the second parameter is the '0.00%' presentation format. The final values will be presented as a percentage.

The expression inside the Discount*SalesAmount aggregation function is calculated for each row before aggregation and the division operation between the aggregations is calculated after the aggregation of each sum() for each item in the dimension of the chart. It's important to know whether an expression needs to be calculated inside or outside of an aggregation function, as both will generate different results. When using expressions on labels and titles, if the result is numerical, use the num() function to format the result.

When using expressions in a dimension, this needs to be done with care. This is because they are resource-intensive. For performance reasons, it's always better to create calculations in the script editor. Sometimes, we need a dimension to be calculated dynamically in the form of range values, or in a Pareto analysis; for example, we need to find the products that represent the A category falling in the top 20% range.

Calculated dimensions are useful for changing the content of existing fields or creating new ones, especially if we don't have access to the script editor. The following example shows how to create an expression that can be used in the employee name dimension that can concatenate two fields: EmployeesFirstName & ' '& EmployeesLastName

Table of Contents for Calculation expressions

Create new playlist

Sign In

Sign Up

Table of Contents for
Calculation expressions