Expand the capabilities of Power BI Desktop on your computer

 In PowerBI

In this guide, we will see the next steps to be taken after installing Power BI Desktop on your computer and an example case: The Calculation Groups.

 

Do you want to implement Power BI in your company?

 

After installing Power BI Desktop, it is recommended to install three very useful external applications. To do this, in the Power BI Help menu, click on the “External Tools” button.

This will open a window in your default browser, Use of External Tools in Power BI Desktop.

In this page you will obtain preliminary information about three tools that, used in conjunction, will allow you to obtain more Power BI functionalities and solve complex situations.

These tools are:

A lightweight editor for SQL Server Analysis Services tabular models developed in .NET/WinForms

Tools to develop and modify DAX queries

Open source tool used to manage Power BI datasets

Occasionally the installation of ALM tools may cause a negative response from the antivirus, in that case it creates an exception for the application.

Once installed, a new menu option will appear in Power BI where the installed tools will appear:


Calculation groups

In a data model in which we have different measures in various tables it may be necessary to create various calculated measures based on these measures, such as

  • The accumulated value of the month to the current date (MTD: Month to Date)
  • The cumulative value of the year to date (YTD: Year to Date)
  • The accumulated value of the previous year.

This means creating as many calculated measures as we want to apply these calculations to, which means a significant increase in work.

Creating a group of calculations will allow us to reduce considerably the work to be done.

To create a group of calculations we can use the external tool Tabular Editor.

To do this we will go to the Power Bi external tools menu, present since the July 2020 version.

When opened, the editor will be directly connected to the data set (Dataset) of the report we are working on at that time, so we already see our measures calculated.

 

To create our calculation group, the first thing to do is to create the folder that will host them:

 

The newly created group will appear as:

 

We can rename it as we see fit, for example Time Calculations.

If we deploy the node we can see that it is a table with a single field with multiple rows based on the calculation elements (Calculation items)

 

 

The calculation elements are equivalent to the calculated measures that we had previously created.

We will start creating calculation elements by right clicking and selecting New calculation Item.

 

We can start with the accumulated value of the month to date or Month to Date, MTD for short.

We have two options, or we start writing our DAX code in the editor section:

 

Or we can go to the PBIX report and select and copy the code we have created for one of our BAT measures:

 

We will copy the code that is to the right of the equal sign (=) and proceed to paste that code into the editor:

 

However, instead of keeping the measure [Qty], we will replace it with the SELECTEDMEASURE() function that returns the measure we are evaluating.

Once created our calculation elements, we will save the changes in the program menu.

 

If we go back to the PBI report, we will see that there are changes and the report needs to be updated.

 

We can see that the element Time Calculations has appeared in our model

 

If we add a matrix to the report and select elements from the calendar table in the rows and place the quantity measure Qty in the values we can see the result in the matrix


If we add the field Name of the calculation group to the columns, the thing changes and the following appears:

 

In a single step we have managed to obtain a matrix in which to start breaking down all sorts of calculations on the Qty measure based on the calculation elements we created in the group.

And all this without having to write all the temporary calculated measures for each measure.

If we add the measure Sales to the values, the matrix begins to show us a complex data structure in a single step:

 

 

Do you have an SME? We help you!

At Emiral we know how much your company is worth, whether it is small or medium sized. That is why we want to offer you the best solutions to keep your business growing.

Marc Garzarán Voyer

Consultor BI freelance en diferentes proyectos. BI sobre Microsoft Dynamics AX

Recent Posts

Leave a Comment