Create SUM Measure from Column

• 2023-02-22
• Morten Lønskov

Applies to:

• ✔Tabular Editor 2.x
• ✔Tabular Editor 3.x

Script Purpose

If you want to quickly create a number of measures that SUM over the columns that you select then this script with do it for you.

Script

Create measures from columns

// Creates a SUM measure for every currently selected column and hide the column.
foreach(var c in Selected.Columns)
{
    var newMeasure = c.Table.AddMeasure(
        "Sum of " + c.Name,                    // Name
        "SUM(" + c.DaxObjectFullName + ")",    // DAX expression
        c.DisplayFolder                        // Display Folder
    );
    
    // Set the format string on the new measure:
    newMeasure.FormatString = "0.00";

    // Provide some documentation:
    newMeasure.Description = "This measure is the sum of column " + c.DaxObjectFullName;

    // Hide the base column:
    c.IsHidden = true;
}

Explanation

This snippet uses the <Table>.AddMeasure(<name>, <expression>, <displayFolder>) function to create a new measure on the table. We use the DaxObjectFullName property to get the fully qualified name of the column for use in the DAX expression: 'TableName'[ColumnName].

Example Output

Figure 1: An example of measures created with this script.