Export (0) Print
Expand All

Measure and Non-Measure Fields in Power View

SQL Server 2012
Important note Important

Please visit the most up-to-date Power View documentation on office.microsoft.com. Power View is now a feature of Microsoft Excel 2013, and is part of the Microsoft SQL Server 2012 Reporting Services add-in for Microsoft SharePoint Server 2010 and 2013 Enterprise Editions.

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.

Note Note

Power View is a feature of SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition.

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.

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.

Note Note

The field in the fields section of the field list is still a non-measure field, but the field in the table in the view is a measure.

Arrow icon used with Back to Top link Back to Top

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).

Note Note

The column label in a visualization for either kind of counted field is the same: Count of <Field Name>. You can tell whether it counts distinct or non-blank values by clicking the dropdown arrow next to the field in the Values box for a matrix or chart or the Fields list for a table.

Arrow icon used with Back to Top link Back to Top

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.

Arrow icon used with Back to Top link Back to Top

Community Additions

ADD
Show:
© 2014 Microsoft