Aggregating values in "virtual" columns created with table constructor within a measure

Question

within a measure I am creating a "virtual" table with several rows and columns. Within the same measure I need to aggregate the values from one of the resulting columns.

My problem is that I can't figure out how to access/refer to a column of the resulting virtual table with aggregator functions like e.g. MAX() or SUM().

Here is the code for creating the table within a measure (= it is not a calculated table in the datamodel):

VAR virtualtable =
        {
             ( "o1", 1, 2, 3 ),
             ( "o2", 4, 5, 6 ),
             ( "o4", 7, 8, 9 ),
             ( "o5", 10, 11, 12 )
        }

Resulting table:

Value1	Value2	Value3	Value4
o1	1	2	3
o2	4	5	6
o4	7	8	9
o5	10	11	12

Trying to sum the values of column "Value2" using SUM( virtualtable[Value2] ) does not work. Any ideas?

sergiom sergiom · Accepted Answer · 2021-01-29T11:44:04

The correct way to SUM the column of a table variable is using SUMX, for instance this works

Sum Val 2 = 
VAR virtualtable =
        {
             ( "o1", 1, 2, 3 ),
             ( "o2", 4, 5, 6 ),
             ( "o4", 7, 8, 9 ),
             ( "o5", 10, 11, 12 )
        }
RETURN SUMX( virtualtable, [Value2] )

Aggregating values in "virtual" columns created with table constructor within a measure

1 Answers