Sign in or start a free trial to avail of this feature.
9. Using Quick Measures
In this lesson, we’ll create more Quick Measures, including a running total, a quarter to date revenue figure, and a comparison with a specified value.
- Running Totals allow you to see how a field has grown over time
- Note that the running total Quick Measure is not compatible with the Date hierarchy in Power BI
- Various time intelligence functions are available, including year-to-date, quarter-to-date and month-to-date calculations
- All of these are common calculations in business contexts
Difference from Filtered Value
- This measure takes one value as a base value and compares other categories to this base value
- In this case, we use the measure to quickly benchmark regions against each other
In the previous lesson we introduced the concept of Quick Measures. In this lesson, we'll look at three more examples of Quick Measures, running totals, time intelligence and filters.
We'll start the lesson by using running totals to study the pattern of transactions over the length of the data set. There may be times of the year where sales are busier and more transactions are recorded. We can see if this is the case by graphing the running total of number of transactions over the course of the year. We'll select the new Quick Measure menu as before, and then select a running total.
As our data set has one transaction for each company, we can count the number of transactions by counting the number of companies. Therefore, we'll drag company name to base value, select the arrow and change the aggregation to count.
We want to count this total over time, so we'll drag date to the field area and click okay.
We'll now create a line chart of the running total.
We'll add the running total field to the values well and add the date field to the axis.
It's important to note that you can't use the date hierarchy with the running totals. To that end, we'll click the arrow besides date in the axis well and switch from date hierarchy to date.
Let's resize the chart so we can get a better looks at the trend.
The line is relatively straight, suggesting our level of transactions is constant throughout the year. Let's move on and create a time intelligence Quick Measure. These Quick Measures can calculate year to date, quarter to date and month to date values, all of which are commonly found in business reports and dashboards. Let's create a chart of revenue and quarter to date revenue. We'll start by dragging the revenue field to the canvas to create a clustered column chart. We'll add the date field to the chart, expand down to the monthly level, and resize the chart so that all columns are visible.
The chart is currently sorted by revenue so we'll select the three dots in the corner to sort by month. We'll select this again to sort it from January to December. We'll now add the Quick Measure. We'll select new Quick Measure and then choose quarter to date total.
The base value is revenue and the date value is the date field.
We'll click okay to create the measure. We'll now add this new revenue quarter to date measure to the column chart. Our chart now shows the monthly revenue figures in green and the quarter to date revenue figures in black.
Finally, we'll consider a Quick Measure based on filters. Suppose we want to benchmark the revenue performance of our regions against each other. One way to accomplish this is to compare with a filtered value, which for us will be the value for one of the regions. Let's select a new Quick Measure again and then select difference from filtered value as a measure to create. The base value here will be revenue and the filter will be region. We now have to choose a region for our benchmark. We'll select Midwest, then click okay to create the new measure.
We'll now create a new clustered column chart. We'll add the new revenue difference measure as the value, date as the axis and region as legend.
Our chart now shows the revenue for each region relative to the Midwest region. Let's move down the date hierarchy to month. We can see that the West region consistently has lower revenue than the Midwest region while the South is consistently ahead. The Northeast region generates more revenue every month except February and December. ' stop the lesson here. As we can see, Quick Measures allow you to easily gain additional insight into your reports. New Quick Measures are frequently added through updates to Power BI Desktop, so this is likely to be area where Power BI grows in the future. In the next lesson, we'll move on and look at groups, lists and bends.