You probably already know what a measure is, but there are a number of points that need to be made when we talk about them and make decisions on how to define them. If we can get on the same page, we’re more likely to be successful with our work.
First off, we can define a measure as being whatever it is you want to measure. That’s all. Here is an example: Amount Sold.
If we are working with a data warehouse, a measure comes from a fact. I prefer we don’t think of them as being synonymous — facts and measures, that is. Let’s take the example of the Amount Sold.
An invoice is made up of line items. Each line item has a total value for that line, probably quantity times price, for example two units at $500 each. This line will total $1000, and because this belongs to an actual transaction that is recorded in our application we call it a FACT and it’s not changeable (unless it’s changed by someone who is using the application).
Here is the example (the highlighted line):
It’s necessary to instantiate our measure, Amount Sold by defining it in code. This type of measure because it’s virtually the same as the fact is called a “base measure.”
It might seem like this adds an extra layer of complexity, but this is important. We consider our facts immutable, perhaps even named the same as they are in the source data. However we assign names to our measures that we use in the context of analytics, hence, “Amount Sold.”
As a practice, I like to keep base measures pure, made from a single fact. In contrast, I like my derived measures to be made up only from measures (not a combination of measures and facts). There is a very good reason to do this, in the context of a Tabular Model. Facts, or “columns” are scalar values and measures are tabular values, or “table values.” This is extremely important if you use the DAX Language.
Let’s move on from that stuff — scalar vs. tabular — and return to the idea of base measures and derived measures. This is a concept more easily understood, and it is a topic that comes up during design.
Base Measures: think of base measures as nearly the same as the facts, but they are renamed for reporting, and can be used to build derived measures. Examples of base measures would be Amount Sold, Number of Sales.
Derived Measures: think of derived measures as being measures made of measures. An example of a derived measure would be Average Sale Amount. It uses the two base measures above, dividing the Amount Sold by the Number of Sales.
Here is one last point about measures. In an SSAS Tabular database, measures are not stored. They are calculated when needed. This is because the Tabular model stores all of data in memory. This is one of the ways that it performs so well.