The TOTAL qualifier is added to aggregation functions to disregard chart dimensions and make the calculation over the entire record set as defined by the current selection state.
In a previous section, we calculated the percentage of FTEs each carrier performs relative to the total number of FTEs and we used a variable (vTotalFTEs) to store the divisor value. Instead of using a variable, the same calculation can be made using the following expression:
Sum([# Equivalent FTEs]) / Sum(TOTAL [# Equivalent FTEs])
When the preceding expression is used in a straight table with Carrier Name as the dimension, the numerator will calculate the value corresponding to each carrier, which will be different on each row of the table. The divisor will calculate the total number of flights made by all carriers, which will be the same for all rows of the table, therefore disregarding the dimension value.
Additional modifiers can be used along with the TOTAL qualifier to disregard only some of the dimensions in a chart, instead of all of them; that is, in cases where there is more than one dimension in a chart.
For instance, if the straight table described earlier, besides the Carrier Name dimension, also has the Year and Month dimensions, we can add any of those dimensions to a list of fields enclosed in angular brackets, and separated by a comma, to specify which of them should the TOTAL qualifier disregard.
Let's take the already created pivot table in the Reports tab of our Airline Operations document and add three new expressions to it to better illustrate how the TOTAL qualifier works. The three expressions we'll add, along with their labels, are:
- Participation to the whole:
Sum([# Departures Performed]) / Sum(TOTAL [# Departures Performed])
This expression will return the percentage of flights for a particular carrier, month, and year relative to the total flights of all carriers, and for all years and months available in the current selection state.
- Percentage versus the carrier's whole:
Sum([# Departures Performed]) / Sum(TOTAL <[Carrier Group], Airline> [# Departures Performed])
This one will return the percentage of flights for a particular carrier, month, and year relative to the total flights performed by that carrier in all years and months available in the current selection state.
- Percentage versus the carrier's whole per year:
Sum([# Departures Performed]) / Sum(TOTAL <[Carrier Group], Airline, Year> [# Departures Performed])This will return the percentage of flights for a particular carrier, month, and year relative to the total flights performed by that carrier in that year, but for all months available in the current selection state that correspond to that same year.
The result is shown in the following screenshot:
