So, I'm going to return to Power Pivot now. The measure we created, I said, lives in the Profit and Loss table. Again, we can see that in the diagram view. If I go down to the Profit and Loss table, we can see the measure there.
Now, we're going to be creating quite a lot of measures, and we don't want them housed in these different tables. Instead, we're going to house them in what are called Measure Tables. These are going to be blank tables that don't contain data but just contain measures. That's going to allow us to keep ourselves more organized. We're going to know by looking at those tables which measures have been created, and then it's going to be much easier for us to retrieve them when we use them in, for instance, Pivot Tables later on.
So, I want to create a Measure Table, and I want to move this measure to it. Now, we've created a table before, and it was the Comparisons table where we typed Plan, Budget, Forecast 1, 2, and 3 into Excel. We're going to do something similar to create our blank table. You're going to go into Excel, just go to any cell, press Ctrl + C, and then return to Power Pivot. Then, in Data View, click Paste, and we're going to call this table PL Measures. Press OK.
So, we have a new table here, PL Measures, and we've just got one blank column with nothing in it. Now, to get our measure into this table, we could copy and paste it into here, which would remove it from here and then house it in here, and that would be fine. But I'm going to show you how we can do it in Excel.
So, if I come out of Power Pivot again, I'm going to go to the Power Pivot tab here, go to Measures, and go to Manage Measures. Now, this is our existing measure. We can go to Edit, and then you can see it in the table name Profit and Loss. I can select here PL Measures and press OK. I close this, and we can see we have a message here which says "Relationships between tables may be needed," and it says that because this Pivot Table is still referencing the Actual Period measure, but now that lives in a new table, the PL Measures table, and that's not connected to our data model.
Now, we never want to connect a Measure Table to our data model, so we can kind of just get rid of this. If I look down here, I'll just collapse that. We have this PL Measures table, and I can expand it, and we have the Actual Period measure and just the blank column. Now, if I go back into Power Pivot, we can see our measure here, Actual Period. So, if I just kind of expand that, and in Profit and Loss, it is no longer there. In PL Measures, if I go to this blank column, right-click it, and then select Hide from Client Tools, if I go to Diagram View, you can see now that column is grayed out. If I go to Excel and then I go down here, we no longer have the PL Measures table here; it's actually jumped to the top, and we now have the sigma sign which denotes that it's a Measure Table or it's a table just containing measures. If I expand that, we can see our measure there.
So, as we go through creating more measures, we're going to house them in this PL Measures table, and it's effectively going to separate these measures from our tables that contain data. I'm actually going to create a second Measure Table now for some sales measures that we're going to create later on. So, if I just press Ctrl + C again and then go to Power Pivot, Manage, and click Paste, I'm going to name the table Sales Measures and press OK. We now have a second Measure Table that we can use later on.
