I introduced measures briefly in our columns versus measures less than a few videos ago, and we spent
the last few lessons working strictly with calculated columns, you can do a lot with calculated columns
and dacs.
However, eventually everything will lead up to the definition of a measure.
We've already defined a few measures through the use of our pivot tables.
Any time you drag a column into the value section of a pivot table, your pivot table will define a
temporary measure based upon whatever function you've selected.
So when we drag the employee wages to our value section and it calculated the sum, it created a measure
called sum of employee wages.
This is a temporary measure, to be sure, but it is a measure nonetheless, and it's based upon the
employee wages.
Calculated column measures represent aggregations or measurements of information contained within one
or more columns, and they can be written in DACs just like any other formula.
In fact, it's fairly common to create explicit measures for calculations such as are some of employee
wages and then hide the original employee wages column to prevent users from creating inappropriate
measures when they drag them to the value section where they pick, say, the counter average functions
where the data isn't structured appropriately.
With that in mind, let's update this pivot table to use proper measures rather than these temporary
ones.
I find measures are easier to create on the Excel side than the parapet side.
By going to the parapet, reben go to the measures, dropdown and choosing the manage measures.
I won't click on it just yet.
And instead I'm going to show you how to create measures and the power pivot area really quickly and
then we'll abandon that since it's terrible.
So go ahead and open the data model with the manage button here and go to the purchase orders table.
We're going to click on one of the cells in our calculation area at the bottom.
And I'm going to type the formula, equal some purchase order when I do this and new measures added.
And we can look at the formula bar to see the format.
It says measure one colon equals our formula.
Now, this is awful for a number of reasons.
First of all, the only way to find this measure is to look at the purchase orders table and then look
at our calculation section.
If we go to another table, we wouldn't be able to see the measure anymore.
Now we can rename it by modifying the formula, but that's kind of a pain two and it's prone to errors
in case utility equals.
And then you'll have to fix that as well.
So I'll rename it and then we'll leave the measure here and pop out to ourselves, to the Excel managed
measures view with all of the measures in our data model from top to bottom to allow for easy viewing,
editing and deletion, we can easily select our existing measure and choose edit within the edit window.
I can modify the table that the measure belongs to the measures, name the description, check our formula
and define the category for some easy formatting options.
To me, this window is far superior for managing measures in every way, overwriting them within the
data model, which is the reverse of how the relationships management options are.
We can change the measure name to purchase order total, change the category to currency, set the symbol
and decimal places to the level that we want and I'll drop the decimal to zero and then press.
OK, closing out of our management window, we can modify our pivot table under the purchase orders
table is a new field with effects in front of it called Purchase Order Total.
This indicates that it's a measure so it cannot be used as a filter arrow or a column item.
I'm going to drag this next to our sum of purchase order and we can see from the table that they match.
One major difference, though, is that the format has already been applied and there's no some of that's
been added to the front of our field.
Another difference can be seen if we click on the arrow next to it in our pivot table fields area and
choose the value field settings here under the summarised values tab.
All of the options are great out and disabled.
This measure has been specified and so there's no aggregation formula selection to be made.
As with the other fields, of course you can change the additional calculations on top to get the percentage
of the row or the percentage of the column or a number of other features.
For now, let's go and create our other two measures for sales and wages really quickly and then I'll
discuss the profits measure.
Using the measures dropdown, I can click on New Measure to jump right into creating a new measure.
This time I'll select the sales table and call the measure sales total.
Then in the formula section I'll type equal some sales, but I won't close the parentheses and I'll
add an extra Eagleson.
If I click on the check formula button to get a quick notification of the error, it's not particularly
helpful, but it does provide some quick feedback.
I'll go ahead and close the prince's, check the error again, remove the equal sign and then get a
nice, clean notification.
I'll set it as a currency with zero decimals and press, OK, and then pop back into my management area.
I can see both of my measures and I'm going to go ahead and add the third and final one with the new
button here, which takes us back to that same interface.
I'll knock this one out quick so we can move on to our fourth and final measure.
The last measure we want to calculate is the profit, but it's not based on any calculated columns.
One of the really powerful features about measures is that they can calculate off of each other.
So the profit is simply the sales minus the purchase order total, minus the wage total.
Now I'm adding this to our labor table, but the table isn't really important for the measures.
A common approach is to create a blank table, specifically to store your measure, since so many of
them are independent of the data table supporting them.
So let's go into our data model and create that empty table to do this, I'm going to copy a random
cell and then paste it.
I'm going to name this table, underscore measures.
After doing this, I'll right click on this pointless column and choose hide from client tools.
This is a common practice in Paraguay, and I like to use it in Excel too, and now I can close out
back and Excel.
I'm going to go into the managed majors section and edit each of the measures and assign them to that
underscore measures table.
Once I've done this, we're good to go.
Our measures are easily accessible from this topmost section of our pivot tables field window and now
I can reassign them within the pivot table itself.
Once I've done this, I've rebuilt our report entirely using measures.
This covers the last major feature of power pivot that will cover in this course from here on, we'll
look at DACs and some patterns that we can use with DACS to create some more powerful and useful measures
than simple sum or count calculations.