Yes, using SELECT made me die a little inside too so I designed this method instead. It’s pretty simple, the space saving is not as obvious in the following example but, if you find yourself having to sum down a dimension that is deep within a hierarchy and before you are forced to use Summary Types then this is much better.
Define Lists: di01, di02, di03
Arbitrary length and element names. The lists need not have a top level item.
In my example the lengths of di01, di02 & di03 are 20, 100 & 50 respectively.
Define List: OneState
One element, namely, 'State'. This list also need not have a top level item.
Define Module: Box
Applies to: di01, di02, di03
Define Line Item: Value
Data Type: Number
Fill this Box module with random data.
Cell Count: 100,000
Define Module: Lists to OneState
Applies to: N/A
Define Line Item: di02
Applies to: di02
Data Type: OneState
Formula: OneState.State
Define Line Item: di01 & di02
Applies to: di01, di02
Data Type: OneState
Formula: OneState.State
Cell Count: 2,100
Define Module: Collapse di02
Applies To: di01, di03, OneState
Define Line Item: Sum
Date Type: Number
Formula: Box.Value[SUM: Lists to OneState.'di02']
Define Line Item: Average
Date Type: Number
Formula: Box.Value[Average: Lists to OneState.'di02']
Define Line Item: Max
Date Type: Number
Formula: Box.Value[Max: Lists to OneState.'di02']
Define Line Item: Min
Date Type: Number
Formula: Box.Value[Min: Lists to OneState.'di02']
Cell Count: 4,000
Define Module: Collapse di01 & di02
Applies To: di03, OneState
Define Line Item: Sum
Date Type: Number
Formula: Box.Value[SUM: Lists to OneState.'di01 & di02']
Define Line Item: Average
Date Type: Number
Formula: Box.Value[AVERAGE: Lists to OneState.'di01 & di02']
Define Line Item: Max
Date Type: Number
Formula: Box.Value[MAX: Lists to OneState.'di01 & di02']
Define Line Item: Min
Date Type: Number
Formula: Box.Value[MIN: Lists to OneState.'di01 & di02']
Cell Count: 200
Using the OneState Dimension as a root of all your data aggregation and calculation modules lends you a huge amount of flexibility. As you can see you can extract SUM, AVERAGE, MIN and MAX using this method without any Summation Types or the SELECT function. The source module may or may not have the OneState dimension applied to it so this method can be chained (only relevent for the non-commutative operations of course).
This can be used in many areas of aggregation but also conditional formatting. Using a 3 colour scale you can construct a piecewise affine mapping of the Min to 0, the Average to 0.5 and the Max to 1.