Measure and Non-Measure Fields in Power View

A measure is a formula that model designers create in tabular models specifically for use in a PivotTable, PivotChart, or Power View report that uses PowerPivot data. Measures can be based on standard aggregation functions, such as COUNT, SUM, or AVERAGE; model designers can define their own formulas by using DAX. Power View identifies measure fields with a Sigma ∑ symbol in the field list.

By default fields are either measure or non-measure fields; usually numeric fields are measures, but not always. A field in the field list may look like a number field – it has numbers in it. But just containing numbers doesn’t make it a measure by default. If it contains only integers and no fractions, Power View does not assume it is a measure. Numeric fields like ID fields are not measures; it doesn’t usually make sense to add or average an ID.

But you can change non-measure fields to measures, if you want to aggregate them, and you can change a measure to a non-measure, if you do not want it aggregated.

In this article

Convert a numeric field to a measure

Convert a text (non-numeric) field to a measure

Add a text field to a visualization as a measure

Duplicates and blanks in a text (non-numeric) field

Convert a measure to a non-measure field

Convert a numeric field to a measure

For example in Power View, you might have a Rating field, with ratings from 1 to 5. You add it to a table in a view and think that therefore you should be able to convert the table to a chart, but all the chart icons are grayed and disabled. You notice the field has no Sigma ∑ symbol next to it, and that there is a row in the table for every rating of every item.

1. In the Table fields or Values box in the layout section of the field list, click the drop-down arrow next to a numeric field.

   Note that Do not summarize is checked.

2. Check one of the aggregation options: Sum, Average, Minimum, Maximum, or Count.

   Let’s say you click Average. Here are things that have changed:

   • You now see just one row for each item.

   • The numeric field is an average of the values.

   • In the Values box in the layout section of the field list, the field name has a Sigma ∑ next to it.

   • The chart icons in the Visualizations Gallery are enabled.

Back to Top

Convert a text (non-numeric) field to a measure

You can convert a text field to a measure so that you can count it, and then display the count in charts. For example, let’s say you want to know how many products are in each product subcategory. You add the Category and Product fields to a table in the view. You see a long list of the products in each category. All the chart types are grayed in the Visualizations Gallery because the matrix has no measures.

1. In the layout (lower) section of the field list, click the drop-down arrow next to a non-numeric field.

   Note that Do not summarize is checked.

2. Click Count.

Add a text field to a visualization as a measure

You can also make a field a counted field as you add it to a visualization, rather than adding and then converting it. This can speed up performance, because Power View does not have to fetch all the items in the field.

• For a matrix or chart, drag a field from the fields (upper) section of the field list and drop it in the Values box.

• In the fields section of the field list, click the drop-down arrow next to a non-numeric field.

  • For a matrix or chart, click Add to Values.

  • For a table, click Add to Table as Count.

Doing any of these automatically adds the field as a counted field.

Duplicates and blanks in a text (non-numeric) field

When you set Power View to count the values in a text field, by default it counts all the rows that contain data: It counts duplicate values, but not blanks. You can set it to instead count only unique (distinct) values, including blanks.

• Click the drop-down arrow next to the field in the layout section of the field list, and click Count (Non Blank) or Count (Distinct).

Back to Top

Convert a measure to a non-measure field

There may be times when you do want to see every value of a measure, rather than aggregating it by summing or averaging, for example. You can only do this in a flat table. The option to change a measure to a non-measure does not exist in any other visualization.

1. In the Table fields or Values box in the layout section of the field list, click the drop-down arrow next to a measure.

   Note that an aggregate, such as Sum, is checked.

2. Click Do not summarize.

   Each item may now have multiple rows, each with a different amount.

Back to Top

See Also

Concepts

Power View (SSRS)