--- title: "Aggregation and allocation methods" slug: "aggregators-and-allocators" updated: 2026-04-23T15:00:40Z published: 2026-04-23T15:00:40Z stale: true --- > ## Documentation Index > Fetch the complete documentation index at: https://kb.pigment.com/llms.txt > Use this file to discover all available pages before exploring further. # Aggregation and allocation methods This article describes the various allocation and aggregation options in Pigment, for use with [modifiers](/v1/docs/dimension-modifiers) and [advanced aggregation](/v1/docs/totals-aggregator-panel#advanced-aggregators). For more information on aggregation and allocation use cases, see [Use cases of aggregation and allocation](/v1/docs/use-cases-aggregation-allocation). ## Allocation Methods #### Constant This method takes the value from the source Metric or Property and applies it to every Item within the replacement Dimensions. This is the default behavior if no allocation method is defined. `Source_block[BY Constant: mapping attribute ]` Supported Data Types - All Types Return Data Type - same as Input #### Split This method takes the value from the source Metric or Property and distributes it evenly across the Items within the replacement Dimensions. `Source_block[BY split: mapping attribute ]` Supported Data Types - Number, Integer Return Data Type - Number ## Aggregation Methods #### Sum This method returns the sum of the source values from the aggregated Dimension. This is the default behavior when using BY. `Source_block[BY: mapping attribute ]` `Source_block[BY SUM: mapping attribute ]` Supported Data Types - Number, Integer Return Data Type - same as Input #### AVG This method returns the average of the source values from the aggregated Dimension. `Source_block[BY AVG: mapping attribute ]` Supported Data Types - Number, Integer Return Data Type - Number #### MEDIAN This method returns the median of the source values from the aggregated Dimension. - If the data has an odd number of observations, the middle number is returned (after arranging in ascending order) - If the data has an even number of observations, the average between the two center values is returned `Source_block[BY MEDIAN: mapping attribute ]` Supported Data Types - Number, Integer Return Data Type - Number #### STDEVS This method returns the sample standard deviation of the source values from the aggregated Dimension. `Source_block[BY STDEVS: mapping attribute ]` Supported Data Types - Number, Integer Return Data Type - Number #### STDEVP This method returns the population standard deviation of the source values from the aggregated Dimension. `Source_block[BY STDEVP: mapping attribute ]` Supported Data Types - Number, Integer Return Data Type - Number #### MIN This method returns the minimum value of the source values from aggregated Dimension. `Source_block[BY MIN: mapping attribute ]` Supported Data Types - Number, Integer, Date Return Data Type - same as Input #### MAX This method returns the maximum value of the source values from aggregated Dimension. `Source_block[BY MAX: mapping attribute ]` Supported Data Types - Number, Integer, Date Return Data Type - same as Input #### FIRSTNONBLANK This method returns the first non blank value of the aggregated Dimension. `Source_block[BY FIRSTNONBLANK: mapping attribute ]` Supported Data Types - All Types Return Data Type - same as Input #### FIRSTNONZERO This method returns the first non zero value of the aggregated Dimension. `Source_block[BY FIRSTNONZERO: mapping attribute ]` Supported Data Types - Number, Integer Return Data Type - same as Input #### FIRST This method returns the first value of the aggregated Dimension. `Source_block[BY FIRST: mapping attribute ]` Supported Data Types - All Types Return Data Type - same as Input > [!NOTE] > ℹ️ Note > > Use of FIRST can add density to your Metric, slowing down performance. > > In some cases, this performance loss can be improved by using FIRSTNONBLANK instead. > > When you use FIRST, Pigment provides a Performance Hint in the formula bar. See [Modeling Hints](/v1/docs/dimension-alignment-indicator) to find out more. To learn about the importance of sparsity, see [Leverage sparsity for optimal performance](/v1/docs/leverage-sparsity-for-optimal-performance). #### LASTNONBLANK This method returns the last non blank value of the aggregated Dimension. `Source_block[BY LASTNONBLANK: mapping attribute ]` Supported Data Types - All Types Return Data Type - same as Input #### LASTNONZERO This method returns the last non zero value of the aggregated Dimension. `Source_block[BY LASTNONZERO: mapping attribute ]` Supported Data Types - Number, Integer Return Data Type - same as Input #### LAST This method returns the last value of the aggregated Dimension. `Source_block[BY LASTBLANK: mapping attribute ]` Supported Data Types - All Types Return Data Type - same as Input > [!NOTE] > ℹ️ Note > > Use of LAST can add density to your Metric, slowing down performance. > > In some cases, this performance loss can be improved by using LASTNONBLANK instead. > > When you use LAST, Pigment provides a Performance Hint in the formula bar. See [Modeling Hints](/v1/docs/dimension-alignment-indicator) to find out more. To learn about the importance of sparsity, see [Leverage sparsity for optimal performance](/v1/docs/leverage-sparsity-for-optimal-performance). #### ANY Returns TRUE if at least one aggregated item is TRUE, else FALSE. `Source_block[BY ANY: mapping attribute ]` Supported Data Types - Boolean Return Data Type - same as Input #### ALL Returns TRUE if all aggregated items are TRUE or BLANK, else FALSE. > [!WARNING] > If all aggregated items are BLANK, then the BY ALL result will also be BLANK. Because Pigment’s engine is sparse, blank values are not stored in the database. `Source_block[BY ALL: mapping attribute ]` Supported Data Types - Boolean Return Data Type - same as Input #### COUNT Returns the number of aggregated items (BLANK cells are not included). `Source_block[BY COUNT: mapping attribute ]` Supported Data Types - All types Return Data Type - Integer #### COUNTBLANK Returns the number of BLANK items in the aggregated dimension. `Source_block[BY COUNTBLANK: mapping attribute ]` Supported Data Types - All types Return Data Type - Integer #### COUNTALL Returns the number of aggregated items (BLANK cells are included) `Source_block[BY COUNTALL: mapping attribute ]` Supported Data Types - All types Return Data Type - Integer #### COUNTUNIQUE Returns the number of unique values in the aggregated dimension (BLANKS not included) `Source_block[BY COUNTUNIQUE: mapping attribute ]` Supported Data Types - All types Return Data Type - Integer #### TEXTLIST Returns the list of aggregated text values, separated by a comma. `Source_block[BY TEXTLIST: mapping attribute ]` Supported Data Types - Text Return Data Type - same as Input