In this course, we're going to be creating a lot of measures, and it's not unusual at all in a Power BI model to have 20, 30, 50, or over 100 measures. We've seen with measure branching just how quick and easy it is to create new measures, and you can find that before you know it, you're just swamped with them. You've just got tons of them.
So, my advice is to organize your measures into measure tables. This is going to help you locate measures for use in visualizations and also remember which measures have already been created. You can imagine if you created 100 measures and they're kind of sitting all over the place. It can be really difficult to retrieve the ones that you want for visualization or even remember what you've created so far, and so you might end up duplicating your measures.
We want to get organized, and the way to do this is to use measure tables. Now, you can see in this image a load of measures. We can see COGS variance, an exact summary, gross profit versus plan, gross profit percentage variance, etc. Like I said earlier, we can identify these measures because they've got the little calculator symbol. At the very bottom, you can see we've got two tables, so we have the table symbol, but what you'll note is that at the top we have this P&L Actuals, P&L Plan, and P&L Variances, and a little further down, Sales. We can see there's a calculator, but it almost looks like there's a second calculator behind it. These are measure tables.
We create measures and then we can actually file them away into some kind of relevant measure table. In this example, I'm showing a P&L Variances measure table, and stored inside it are all our measures relating to variances. You can see this is a very organized way of arranging our measures.
For reference, in these slides on the right-hand side, I've got the technique for how to create these measure tables, but it's much easier just to jump into Power BI and show you. We created our first measure, this Amount measure, and we had the little calculator symbol to show us that it's a measure and it's just living in a table. Now, if we were to keep on going and creating loads more measures, we might end up with 100, and they would just live in whichever table we had selected at the time. So, we actually want to organize things with measure tables.
Although we don't really need it now because we've only got one measure, I'm going to set up our first measure table, and then we're going to start filling that up with measures as we go along. To create it, we go to Home and then we go to Enter Data, and then we can name this measure table. We're going to name it P&L Actuals and then we're going to press Load.
You can see it's created P&L Actuals here, and in it, it just has this Column1, which is a blank column. Now I can take my measure here, go up to the Home tab, and change this to P&L Actuals, and you'll see it's moved from here into the P&L Actuals table. So now we've got the Amount measure and we've got this Column1. I can go to this Column1, right-click on it, and delete it from the model. Press OK, and now you'll see this has actually moved up. Whereas this table, P&L Actuals, previously had this symbol, it's actually changed to what looks like two calculators. This essentially shows it's a measure table, and the measure tables always live at the top of this Fields pane. Inside it, we have our first measure, Amount. I can collapse that or expand it, and we can just chuck in lots of measures. If we want, we can create more measure tables and then chuck in more measures to those. That's really good practice for starting to organize our measures because we're actually going to be creating quite a lot of them in this course.
