Quick Measures Power BI: Year-To-Date Total Calculation

No comments

In this guide, I’ll briefly discuss how to use Quick Measures in Power BI. Quick Measures allows you to create DAX measures using a point-and-click method. Additionally, it enables you to quickly explore a lot of different formulas and formula combinations. You may watch the full video of this tutorial at the bottom of this blog.

Before I dive in, I just want to say that I don’t personally use Quick Measures often. I find that creating DAX measures the normal way is not very complicated and will benefit you in the long-run.

But I do get questions about this feature so I’ll go over what you can do with it here.

Using The Quick Measures Feature

Through point and click, Quick Measures enables you to create different Key Measures such as the ones I’ve created below. 

DAX measures created without the use of Quick Measures

To get started, I’m going to click the feature on the task pane.

Click the Quick Measures feature on the task pane

Then, I’m going to decide what I want to create.

In this tutorial, I’m going with a Year-To-Date Total calculation.

Using Quick Measures, I just need to click on the Year-To-Date Total under Time Intelligence from the Calculation drop-down menu.

Setting up the Quick Measure for year-to-date total

Next, I need to select a field as my base value or the measure I want calculated. In this case, I’m going to plug-in Total Sales by dragging the measure for Total Sales to the Base Value field.

Dragging the total sales measure into the base value field

I’m also going to drag the Date column to the Date field.

Dragging the date column into the date field

After filling out the necessary fields on the Quick Measures dialogue box, I’m going to click “OK.” 

You’ll notice here that the Total Sales was stored in the Customers’ Table because I clicked on that particular Home Table.

Placing the total sales year-to-date measure on the key measures table

From here, I can easily change where the Total Sales measure will be stored by clicking on the Home Table drop-down menu and choosing Key Measures. This is how straightforward the Quick Measures feature is.

The total sales year-to-date measure on the key measures table

The Pros And Cons Of Quick Measures In Power BI

One of the advantages of using Quick Measures is that it is easy to use, you just have to click and drag fields as I’ve demonstrated earlier.

You can utilize it to do Totals, Time Intelligence, Filters, Aggregates Per Category, Text, Mathematical Operations, and Concatenated Lists among others.

But the thing is, you can achieve the same results using DAX functions. 

I can come up with those measures by using table functions and by having a thorough understanding of how DAX core functions work in Power BI.

For example, in using Quick Measures for simple Filters, you can use the FILTER function instead as I’ve discussed in previous tutorials.

Take a look at our Total Sales Year-to-Date calculation.

First of all, the information below is way more complicated than it needs to be. There is no need for an IF statement, an ERROR statement, and an ISFILTERED statement. 

The complicated formula of  a total sales year-to-date quick measure

What’s more, Quick Measures uses TOTALYTD whereas I prefer to use DATESYTD inside of CALCULATE. You also don’t need the dotted date highlighted below.

Quick measures use TOTALYTD instead of DATESYTD inside of CALCULATE

What this formula tries to do is cover every possible scenario where the Quick Measure might be used. It’s comprehensive but it makes things more complicated than it’s supposed to be.

Consider this Sales Month-to-Date measure I created without using Quick Measures:

A sales month-to-date calculation without using quick measures in Power BI

The formula is much more concise and easy to follow.

What’s more, I can use a technique that will enable me to calculate Sales Year-To-Date from Month-To-Date without the help of Quick Measures.

I can do this by simply changing a single letter in the formula highlighted below, from DATESMTD to DATESYTD. You can’t do this with Quick Measures in Power BI.

Changing DATESMTD to DATESYTD without using quick measures in Power BI

You can see that with formula patterns, you can copy and paste formulas and then change a single element to come up with a new calculation.

Take this example of Cumulative Sales. The DAX formula pattern can be modified to compute Cumulative Sales Last Year.

This, I think, is a better way to use Power BI.

Using a DAX pattern to calculate cumulative sales last year without quick measures

Another reason why I’m hesitant to recommend Quick Measures is that I believe it’s important to know what a formula is doing.

I don’t think it’s good to simply come up with calculated results automatically.

In doing so, you may not be able to tell when something is wrong with your data because you’ve failed to gain an understanding of what’s going on.

This scenario can happen very easily with Quick Measures. 

Lastly, I want to point out that an in-depth knowledge of DAX formulas will enable you to write formulas quicker than you can click and drag fields in the Quick Measures feature.

You can start with simple formulas and branch out to perform more advanced calculations.

Conclusion

Using Quick Measures in Power BI is as easy as clicking and dragging. You only need to select the measures you want to create and this feature will work on it for you. But you have to be mindful when using this feature because it has its limitations. 

It is still essential for you to get the hang of DAX to create quality reports on Power BI. Having a high level of understanding of its core functions will allow you to implement calculations properly.

Now that I’ve shared its pros and cons, I encourage you to try the Quick Measures feature yourself and weigh in. Give it a go and play around with it. You won’t break anything.

All the best,

Sam

Membership Banne

***** Related Links *****
Quick Measures – How To Use Them In Power BI Models
Check Out Our Re-Imagined DAX Formula Reference Guide

DAX Time Intelligence Examples In Power BI

***** Related Course Modules *****
Ultimate Beginner’s Guide To DAX
DAX Formula Deep Dives
Mastering DAX Calculations

***** Related Support Forum Posts *****
Quick Measures Using Averages
Measure Table Refresh Error
Quick Measures Gallery in Power BI Community – Not What You Think
For more Quick Measures queries to review see here…..

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.