In this series of blog posts, we will be examining some of the key features of Power BI Desktop, Power Query and the Power BI Service needed to pass exam DA-100: Analyzing Data with Microsoft Power BI.
In this Power BI training post, we will examine DA-100 exam – Design a data model: Define quick measures.
Power BI’s Quick Measures feature is designed for less experienced Power BI users who would like to add sophisticated DAX functionality to their data models. It requires no knowledge of DAX; you simply make a few choices, and Power BI creates the DAX measure for you.
We will take four examples from some of the different categories which the Quick Measures feature makes available. We will be using a very basic data model. We have some sales figures, which are divided up by branch, account handler, product range, and product.
In our first example, we want to display in this table visual, the average Revenue contributed by the account handlers at each branch, and we are going to use the Average per Category Quick Measure to do this.
Quick Measures are created in the same way as measures, we can either use the button on the ribbon, (just click on Quick measure), or to make sure that the quick measure goes into a certain table, right-click on the table and choose New Quick Measure.
In the Quick Measures dialogue, we make a choice from the calculation dropdown. For this first quick measure, we want Average Per Category.
Our base value is the metric that we are measuring, and this is going to be Sum of Revenue. If you do not want to SUM, just click on the arrow and choose a different aggregation.
Next, we want the category within which we are going to obtain the average. This is going to be the Account Handler column.
That is all there is to creating our quick measure. We click OK, and then we have a new measure, and from now on, there is absolutely no distinction between the measure created by the Quick Measures feature and an ordinary measure which you yourself create.
Let us add the new measure into our table visual and let us shorten the column heading somewhat.
So, what the Average per Category quick measure has given us is the ability to see the average sale contributed by each account handler, branch by branch.
Let us move on to another example. Year-to-date, and this feature is available as a quick measure in the Time Intelligence category. So, again, let us right-click on our table, and choose New quick measure.
And this time, let us select Time Intelligence > Year-to-date.
Again, our base value is Revenue, and then we need to specify the date field within our data model, and here we have a restriction.
If we attempt to use the date built into the data model, you will see that we get an error. Only Power BI provided data hierarchies, or primary date columns are supported. So, basically, we either need to activate Power BI’s Auto Date/Time feature, or we need to have a dedicated date dimension table of our own.
Let us, therefore, cancel the operation and use the first solution, which is to go into File > Options and Settings > Options, and in the Current File > Data Load section, we simply activate Auto Date/Time.
You will now see that our date column has an automatic date hierarchy associated with it, and this date column can now be used in our quick measure. So, again, we right-click and choose New Quick Measure > Year-to-date Total. Our Base Value is the SUM of Revenue; and, for Date, we can now use our automatic date dimension with no problem.
Let us add our new measure to our table visual and what we will need to do is to get rid of the dates that we have here and use the individual columns from our date hierarchy. So, for example, we can have Year, and we then might have Month.
So now we can display our Year-to-date measure with no problem. So, we can see that the year-to-date accrues in 2015, and then it starts again in 2016, accrues up until December, and then restarts in 2017, and so forth.
If we attempt to display an ordinary date, you will notice that we still do have a problem.
This will only work if the Revenue Year-to-date is broken down using the date hierarchy, though you can obviously decide on the granularity within the hierarchy.
So, if you did want to display a date, the only way would be to have your own date dimension and use that instead. So, let us just look at doing that by creating a barebones Date Table using the formula: Date Table = CALULATEAUTO().
This formula gives us a one column date Table, which is fine for this demo, and let us finish by changing the data type of the column from Power BI’s preferred Date/time to an ordinary Date column.
Next, we will switch across Model mode and create a relationship between ‘Date Table'[Date] and Sales[Date]. Also, for the moment, let us just get rid of the Revenue YTD measure, and we will recreate it in a moment.
Next, let us deactivate our Auto Date/Time feature, and then let us add our regular date (‘Date Table'[Date]) into our table visual.
Then one final thing that we need to do to complete the setting up of our Date Table is to right-click and choose Mark as Date Table.
Then, select the date column, which of course is the only column in our table.
Now, let us recreate our Revenue Year-to-date. So, New Quick Measure > Time Intelligence > Year-to-date. We specify Revenue as the Base Value and ‘Date Table'[Date] as the Date, and we can now successfully create our Revenue Year-to-date quick measure.
We can see the accrual up until the end of 2015, and then from the first of January, our Revenue starts increasing again.
Let us move on to another example. This time, we will look at using the Percentage Difference From Filtered Value quick measure to see what percentage of a benchmark has been achieved by each branch.
In this example, we will say that one branch is the benchmark, and we want to show what percentage the other branches have done in relation to that benchmark branch. And let us choose Liverpool as the benchmark branch.
So, again, right-click New Quick Measure. Then we click Filters > Percentage Difference from Filtered Value. Again, we use Revenue as our Base Value, and we will treat blanks as zeros since we are talking about numbers. We specify the column on which we want to apply a filter, which is the Branch column. And, finally, the filter that we want is Liverpool.
We can then add this measure to our table. And of course, Liverpool comes up as 0%. Any branch which has generated less revenue than Liverpool has a negative percentage; and for any branch which has generated more, for example, London, we can see how much more than Liverpool they have done.
Let us move on to our final example, and for this one, we will look at adding star ratings based on the Revenue achieved by different account handlers.
So again, New Quick Measure. Then, let us scroll down to our Text category and choose Star Rating. Again, we use Revenue as our Base Value. Let us have 10 as the Number of Stars and based on the sort of figures that our account handlers are achieving, let us have 1000 as the lowest star rating, and 7500 as the highest.
Now, let us add our Star Rating into our table visual.
We can see that the two people who are above seven and a half 1000 have 10 stars, and as we scroll down, we have fewer and fewer stars assigned to our salespeople.
So, hopefully, that gives you an idea of how Quick Measures work. You will notice that I have not discussed the syntax. That is not what this topic is about. It is really about the mechanics of using Quick Measures, getting used to the different categories and the choices that you make within the Quick Measures dialog box.