Creating Measures in Excel


Measures are standardized, calculated units that quantify values. They’re important for describing data and creating visualizations.

To take someone’s measure, to evaluate their character or capabilities: he took her measure as a potential employee.

A semifinite measure on a measurable set is a countable, translation-invariant, finitely additive function. Lebesgue measures and circular angle measures are examples of such measures.

Choosing the Right Measures

Measures are critical for understanding company performance. But the right measures provide insight into strategic management; the wrong ones waste time and resources. It’s easy to report on too much data, so selecting the right metrics requires careful consideration and discussion. It’s also tempting to choose data that appears interesting or impressive. For example, a service availability of 98% is impressive, but it doesn’t translate into the business context of “enabling our business colleagues to produce 1000+ widgets for three consecutive months.”

To avoid the temptation to select the first measures that look good, start by applying a strategic framework. Use a framework like the SMART (specific, measurable, achievable, relevant, and time-bound) framework to identify and select meaningful measurements. Then talk about your measures with your colleagues to ensure that they resonate with them and reflect the MVGs from their perspective. Finally, confirm that the measures can actually be measured. This may be the most important step in ensuring that your measurements are effective.

Creating Measures in DAX

Creating measures is a powerful feature that allows users to perform complex calculations and insights into their data. However, it is important to follow best practices and troubleshoot issues when using this feature to avoid pitfalls that can reduce performance.

Unlike calculated columns, which are pre-calculated during data loading and stored in the data model, measures must be applied at runtime by the user. This makes them more flexible than calculated columns, but they can also increase the size of the data model and slow down performance if not designed properly.

To create a measure, you must use the MEASURE keyword and define the table on which it will be applied. Then, you must declare an expression that aggregates the data and return a value. This is done using DAX functions such as SUMX, AVERAGEX, and COUNTX. For example, a measure might be defined as COUNTROWS(Sales) + (DiscountPricePerUnit)/(ListPricePerUnit). The measure must always return a value in the filter context in which it is used.

Creating Measures in Excel

The distinction between dimensions and measures is an important one. A measure conveys very little information on its own, but is useful when combined with other dimensions. Its most basic function is to add up numbers, and it can be further augmented with other functions such as count, average, sum, etc.

Creating your first measure is easy in Power Pivot. You can do this in either the Pivot Table or Data Model view. Creating a measure is much like adding a field, except that you need to drag it into the Values section of the Pivot Table.

Once added, a measure becomes a field and is automatically grouped with other similar fields in the Values section of your Pivot Table, called the Measure Group. This allows you to easily sort your pivot table by a single measure, avoiding the need for complex filter and slicer logic. Measures are also portable, which means they can be used in other tables and reports.

Creating Measures in Power BI

To create a measure, select the table that contains the metric you want to add and then click New Measure on the ribbon. Then, enter a name for the measurement and define the calculation formula using DAX syntax.

You can use the DAX formula suggestion feature to help you write your formulas. This is helpful because it eliminates the need to manually type your formula and helps you avoid mistakes.

When you’re finished, you can use your measure in any visualization by dragging it into the values area of a report. Your measure will update automatically as you filter or change the context of your report.

Creating effective measures is a critical part of Power BI and requires a thorough understanding of how to use the tools available. Formatting your measure table and adding clear descriptions improves usability and makes it easier for users to interpret and understand the data. It also ensures that the metrics are accurate and up to date.

Posted in News.