The administrator can set the Enable Distinct Total Per Measure default behavior in Admin > System Management > Configuration > (5 clicks on the logo) > Base Configuration > Pivot Table. When enabled, duplicate values are eliminated before the aggregation is calculated.Īnalytical Engine calculates the totals, based on distinct values (eliminating any many-to-many impact, and avoiding counting values multiple times.) There is a new Total calculation type - Distinct Totals. However, they are a big problem when processing numbers because you cannot "sum distinct".
Duplicates are not a big problem when processing text, because you can always "count distinct". The process of denormalization of the data sometimes creates duplicates. In the bottom example, Average is used to calculate the Subtotal, and therefore calculates the average of all the average revenue values in the rows above the Subtotal. This represents a true Subtotal of the average revenue for the Asia region. In the top example, using the Auto option, the Subtotal for average revenue aggregates all the sales and revenue data. The other custom options (Sum, Min, Max, etc.) calculate only the values in the rows above the Subtotal, based on the data presented in the Pivot table, in place of the original formula set in the measure. This means that Sisense applies the same formula set in the measure to the entire data. The default option is Auto, which aggregates all the data, sending a separate query to the ElastiCube or data source. To do this, click the header of the desired value in the table, click Subtotal By and select the method.
You can choose different Subtotals in each field.