Skip to main content

Introduction to Calculation Groups

Image
By

Let's consider a scenario where you have 10 fields and the request is to create all-the-time intelligence measures such as MTD, 3M, QTD, and YTD. So as a BI developer in total, you need 40 measures. What??? A long list of measures doesn't look good to me. So can we avoid it? Absolutely yes we can avoid it using calculation groups.

How does calculation group? It is actually available in an external tool called "Tabular Editor". You can download it from here. Let's get our hands dirty. I am creating a table with Category and Total Sales. From time intelligence I need MTD and YTD. You can do it with simple measures but let's try calculation groups.

Under external tools, you can find Tabular Editor. Once you open it you can see the tables you are using. To create a calculation group you can right-click on the table and under create you can find the calculation group or you can do the same with the help of the shortcut (Alt+7). I am naming the group Time Intelligence.


There will be different calculation items that are part of a particular calculation group. Right now we only created a calculation group now we will create items. You can do it just by right-clicking the first item will be MTD. For this, the expression will look something like the below image.


Let's understand what we are actually trying to do with this measure. The selected measure can be any measure (in this case it is total sales). When you select MTD it will provide you with the results of MTD sales. Similarly, we did the same with YTD. Just replace the DatesMTD with DatesYTD. Doesn't it look familiar? You are just doing it once for all the measures you have.


Once you created this just save it from the top and refresh your Power BI report. You can see we now have another table named "Time Intelligence". Let's see the fields in this table. We have 2 fields Name and ordinal. The name refers to the calculation item you created in the external tool.

Protip- Ordinal refers to the sorting of your calculation items and generally, this column is hidden. 

Let's take a look at how you can use the calculation group in the dashboard. I have a table with sales associated with every category. I have created a slicer that includes the calculation items we created once you select MTD the values in the table get adjusted to MTD values and similarly for YTD. Just to double-check whether we are getting the right results I have created basic DAX for MTD and YTD.


The value in the card is calculated via DAX in Power BI and the value in the table is calculated via the calculation group. The calculation group does work but what if I need to format it using a specific expression. It can be done in Tabular Editor too. We will cover this in future blogs. 

Using this can help you to reduce the redundant task of creating measures but also it will affect the performance of the report. One key thing you should keep in mind while using the calculation group is it only works with explicit measures, not with the implicit measures that are already created by Power BI. To know more about explicit and implicit measures you can refer to this link. 


Thanks for Reading Let's connect on LinkedIn. For more such blogs and pro tips do follow us



Comments

Post a Comment

Popular posts from this blog

Ultimate Beginners Guide to DAX Studio

By
There are zillions of external tools available with Power BI but DAX Studio is one of the most commonly used tools to work with DAX queries. It is a perfect tool to optimize the DAX and the data model. In this blog let's shed some light on the basic functionalities that can take your report to the next level. ARE YOU READY?  To start you will need the latest version of the DAX Studio. You can download it from their website . Don't worry you don't have to pay for the license. Fortunately, DAX Studio is a free tool As a BI Developer, I am using DAX Studio regularly. Based on my experience I use it for several purposes but in this blog, I will highlight the most common ones. Extracting a dump of all the measures used in your PBIX. Why do we need to do this? It can be used for documentation purposes also sometimes we try to reuse the DAX and such a dump comes in handy in this scenario. How to achieve it? Open the DAX Studio it is located under the external tools once you open t
Post a Comment
Read more

Append v/s Merge in Power BI

By
Let's discuss another problem of the week. As a Power BI user, there are times when you want to combine queries. What are the ways to do so? In most cases, you can attain it by using either append or merge and both serve different purposes. Let's understand what do these terms mean in Power BI and how they are functionally different from each other.  It is quite common to get data from various sources and you need to combine those data depending on a particular column which is common in both tables so that you can add extra information or column to your big table. In such cases, we use merge queries. How to perform merge queries? For instance, I am considering Sample Superstore data and we will merge the returns table to the order table. You will find both merge and append in the home tab in extreme right in the power query editor. ProTip - You will find two options when you click on the drop-down in merge which are merge queries and merge queries as new. When you use merge que
Post a Comment
Read more

Use Relationship in DAX

By
Data modeling is an essential part of creating perfect visuals. While creating complex data models there can be a case where you can find an inactive relationship represented by dotted lines and it occurs because you already have an active relationship between the two tables. But as a developer, you need to use both the relationship. How can it be done? You can use "Use Relationship" in such cases. Use relationship can be added to your DAX and act as a modifier or enhancer for calculation. It activates the inactive relation. But make sure you have an inactive relationship in place before using the use relationship function. Let's see how it works on Sample Superstore data. In my fact table I have two dates- Order date and Ship date. I am making the two relations between my date table and fact table. The relation between the sample superstore (date) to date table (date) is active while the relation between the sample superstore (ship date) to date table (date) is inactive
Post a Comment
Read more