Power Apps – Page 5 – STL

DAX (Data Analysis eXpressions) is the function language in Power BI desktop. When DAX is used to create measures, it can hard to understand the logic sometimes. Especially one function, the Calculate function, can be challenging.

This is the second part of a series of blog posts, which will investigate the mysterious Calculate function.

It reacts differently to row, column, filter, and table context than the other DAX functions, and will be needed for nesting a number of DAX functions.

In this blog post you will see some examples of how the calculate function works in filter context.

Matrix visual

In the example below, you can see sales in a matrix visual. The visual displays total sales for each sales rep, and sales for a specific product. In this example, backpacks are the product.

In the example the sales have been filtered and calculated by using the SUMX function.

Sales of Backpack = sumx(filter(Line_Items,RELATED(Items[Product Description])=”backpack”),[sales])

To do it with the SUMX function, you need to use the FILTER function. We need a Related function to reference the column in the Items table, and the expression is the measured total sales.

Now Using the Calculate Function

Now let us see how to do the same, but this time with the mysterious Calculate function.

Calc Sales Backpack = CALCULATE([sales],Items[Product Description]=”backpack”)

You can now do it with only one function – the Calculate function. This means that you will not need to use the Filter function to filter by the product, and you will no longer need to use the Related function.

See the matrix below. The Calculate measure returns exactly the same as the SUMX measure.

But the mysterious Calculate function can behave may be a way which seems illogical.

Below the matrix has been amended to display sales by product instead of sales rep.

Now the Calculate measure, as you can see above, cannot understand that it needs to filter the table by each product. The SUMX measure can. The Calculate measure just shows the same sales of backpack total for all the products, but it does not sum up all the values in the Total row at the bottom of the matrix.

To get the Calculate function to return the right result, a Filter and a Related function are needed.

Calc Sales Backpack = CALCULATE([sales],filter(Line_Items,RELATED(Items[Product Description])=”backpack”))

Above you can see that the amendment of the Calculate function now shows the expected result.

But can this special behaviour be useful?

You could be in a situation, where you want to compare the sales of backpack up against the other products. Following on, the next example shows a percentage difference of backpack sales versus other products which needs to be visualised.

Backpack % of other products = DIVIDE([sales],CALCULATE([sales],Items[Product Description]=”backpack”),0)

The measure above will do the job. The Calculate measure without the Filter function or the Related function will return the total sales of backpack in each other product’s rows in the matrix. So that sales for each product will be measured against the sales of backpacks.

Above you can see the result of this approach, and of course the percentage difference from backpack and backpack will return 100%.

Conclusion

I call the Calculate function the mother of all DAX functions. It is the most important DAX function (my personal opinion), but to get the most out of it you will need to understand, how the function reacts to row, column, filter, and table context.

In the next blog post in this series, you will see how the mysterious function is needed for use of date intelligence in Power BI desktop measures.

The Analytics Pane in Power BI desktop is an amazing tool. You can use it to create a variety of different analytical lines inside your report visuals to highlight trends, target lines and forecasting.

This blog will explain how to create these analytical lines and show how useful they are in gaining insights into your data

Why use Analytics?

A report visual is great at representing key summary information such as the quantity of products sold every month. But what if you wanted to check how well your sales were meeting targets over time? You could manually look at each data point on a line chart, for example, but a more efficient solution would be to create a customised ‘target line’ within your line chart. This would instantly provide a visual which displays performance vs. target over a period of time. You will therefore become more efficient as you analyse the data. Below is an example:

How to get started

Create a line chart based on a value field over time e.g. ‘Quantity’ by ‘Order Date’

With the chart selected, click on the ANALYTICS icon in the visualisation pane (see below)

Expand the ‘Y-axis Constant Line’ section and click on ‘+ Add Line’
Double click where it says ‘Y-axis Constant Line’ in the field and type TARGET LINE
Expand the ‘Line’ section to show the options below:

Set the following details to your line:
1. VALUE to 45000
2. COLOUR to Black, for example
3. TRANSPARENCY to 0%
4. STYLE to ‘Dotted’
Collapse the ‘Line’ section and turn on ‘Data label’ to reveal the expanded Data Label section:

Set the following label details to your line:
1. STYLE to ‘Both’ i.e. both ‘Data Value’ and ‘Name’
2. COLOUR to Black, for example
3. DISPLAY UNITS to ‘Thousands’
4. DECIMAL PLACES to 0

Other Analytical Lines

Notice in addition to the ‘Y-axis Constant Line,’ there are many other analytical lines you can apply to your visual some of which are described below:

TREND LINE: turn on to show an upwards/downwards trend – especially useful for analysing performance data over time
MIN LINE: click on ‘+ add line’ to show the lowest data point on a line chart – really helpful to identify the ‘bottom line’ of any business
FORECAST LINE: turn on to show a ‘projected’ line appearing after the last data point. This line can be set over an extended time period of your choice – great for any future business planning

Finally, the analytical lines are only available in specific visuals. They are:

LINE CHART
COLUMN CHART
BAR CHART

Conclusion

Trends, targets, and forecasting are one of many other analytical lines that can really enable you to track and monitor your performance data more efficiently. Using the Analytics pane in Power BI will definitely help to achieve this goal. This will enable you to work on your business’s profitability gains.