.png)
Measure Settings Dialog Box (Custom Aggregation)
The Measure Settings dialog box enables you to create or change a measure that uses a custom aggregation by typing a formula in the Data Analysis Expression (DAX) formula language. To access this dialog box, in the Excel window, on the PowerPivot tab, in the Measures group, click New Measure or Measure Settings.
A measure is a custom, dynamic calculation that you can create, and it is stored together with the data. Anyone who uses the PowerPivot workbook can see the measure and use it in calculations. For more information about measures, see Create a Measure in a PivotTable or PivotChart. Custom aggregations are those that are not based on the aggregations that you can create in a standard Excel PivotTable (SUM, COUNT, MIN, MAX, and AVERAGE).
- Table name
-
Type the name of the table where the definition of the measure should be stored.
Note
If you subsequently delete the table, the measure will be deleted as well.
- Measure name (All PivotTables)
-
Type the name to use for the measure.
This measure name defines the storage name of the measure, which must be unique within the workbook. Any reference to this measure from other formulas should use this name, while the custom name is for display purposes only.
- Custom name (This PivotTable)
-
Type a display name to use for the measure.
This measure name defines the name that is used on the measure for display purposes within the current PivotTable.
- Fx
-
Click to open the Insert Function dialog box and build a formula.
You can also type the formula directly in the text box.
- Check formula
-
Click to verify that the formula has no syntactic errors or semantic errors.
- Formula text box
-
Displays the DAX formula that defines the measure or field values.
You can edit the formula directly in this text box, but the text box does not perform any validation.
- Message and warning text box
-
Displays messages about the status of the formula. If the formula has an error, this box displays information about the problem.
The highlighted portion of the formula is the portion that is most likely to require changing to address the problem.
Format
Select a number format from the list. When you select the Currency, two decimals are automatically added to the Decimal Places box, and the currency symbol is applied to the Symbol box.
Decimal Places
Enter how many decimal places you want the number to display.
Symbol
Select the symbol you want to display with the numbers.
Use 1000 Separator
To use a digit-grouping symbol (such as a comma) to separate the thousand place, select Use 1000 separator (,). For example, check the box for 5400 to display as 5,400.
