How to create measures - 7 steps to follow
This blog page shows (under separate headings) the 7 steps to follow to
create a measure showing the average quantity sold:
The average price of
Amphibian products sold in the
East region in Retail Park centres was 7.76.
Step 1 - choosing where to put your measure
The best place to put a measure is at the level of granularity at which it
applies. I'll see if I can now translate this into English. Consider
the tables for our model:
The transaction table is at the child end of all of the relationships.
It's probably reasonably obvious that if you're going to calculate the average
price across all of the transactions for any pivot table cell's query context,
it makes sense to put the measure in the Transaction table.
Step 2 - choosing a cell for your measure
The next question is in which cell to put your measure's formula:
The area at the bottom of
a table (underneath all of the rows) contains any measures that you type in.
The surprising - to me, at any rate - answer is that ANY cell will do! In
the diagram above I've selected one more or less at random (although choosing
one underneath the Price column may have made more sense, it's
not necessary).
I think this is a failing in the SSAS Tabular interface. There is no
reason to store the measures you create in a grid at the bottom of each table (a
separate storage space on screen would have been much more sensible).
Step 3 - Creating your formula
You can now start typing in your measure:
Type in your formula, using the punctuation characters below.
You should find the following table of characters that you can use useful:
|
Key |
Notes |
|
' |
The apostrophe key brings up a list of tables. |
|
[ |
The opening square bracket key brings up a list of the
columns in a table. |
|
SHIFT + ENTER |
This key combination inserts a new line in a formula. |
|
Space bar |
The space bar inserts blank spaces to make measures easier to read. |
Here's an example of how you can space out your measure formula:
Here I've used spaces and a carriage return to make the measure easier to read.
You can click and drag on the line below the formula bar to make
it bigger or smaller.
Note that for most tables you don't need to encase the name in apostrophes.
Transaction is, sadly, an exception (being a special word in SQ)L, which is why we
need to write it as 'Transaction'.
Step 4 - Naming your measure
When you create a measure, SSAS Tabular automatically gives it a name:
You can change the measure name by overtyping it.
Another way to rename a measure is to press the F4
key if necessary to bring up its properties (note that if you've included spaces
at the start of the measure, this property window may not be available):
You can overtype the measure name using this property.
For this example, I'm going to call the measure Average price:
The name of the measure has been changed.
Step 5 - changing the default formatting
If you include your measure in a pivot table or Power View report, it will
inherit its formatting from your model, so it makes sense to set this now:
Press F4 if necessary to bring up the properties for the measure, and change the default format as shown here.
You can also specify whether you want to display
a thousands separator.
Step 6 - applying filters to test your measure
SSAS Tabular displays the calculated value for a measure based on the rows of
a table currently being displayed:
Initially there are 13,904 transactions, the average price of which is 7.94 pounds.
You can test this by applying a filter to your data:
Here we've clicked on the drop arrow next to the
Quantity column to show only those transactions where 4 items were bought simultaneously.
You can combine filters:
We're now using the same method to show only items for product number 4.
The result of these shenanigans is that we can only see 3 rows, but the average
price is correct:
The average of 10.58, 11.27 and 8.625 is indeed 10.16.
Step 7 - testing your measure in Excel
You could now create a pivot table to show off your shiny new measure:
Here I've dragged the measure created into the
VALUES section of the pivot
table field list (I've also removed the filters
set above).
Excel should show the average price correctly calculated for each cell:
The average price is calculated for each query context.
Note that the formatting is set correctly too.
Now that you know how to create a measure, let's look at the basic DAX syntax
rules.
