Platform Banner

Power BI - Calculate function Tutorial

In this video, you will learn about the calculate function in Power BI.
The video demonstrates how to use the calculate function to filter data and adjust totals without using filters.
By using the calculate function, you can create a new measure that excludes specific regions, such as Europe, from the total quantities.
This will help you customize your data analysis and visualize the results in different visuals.

4:22
3772 views

Available in Microsoft365

Objectifs :

Understand the use of the CALCULATE function in Power BI to filter data effectively, specifically to exclude certain regions from visualizations.

Chapitres :

Introduction to the CALCULATE Function
The CALCULATE function is a fundamental DAX function in Power BI, often used to modify the context in which data is evaluated. Although it may seem limited, it serves as a base function that allows for the application of various DAX functions in different scenarios.
Using Filters in Power BI
In the provided report, a table visual displays sold quantities across different continents. To exclude quantities from Europe, one can easily use the filters pane. By selecting the visual, navigating to the region filter, selecting all, and unchecking Europe, the total adjusts automatically, showing only North America and South America.
Creating a New Measure with CALCULATE
To achieve the same result without using the filters pane, we can create a new measure using the CALCULATE function. Right-click on 'Sales' and select 'New Measure.' A formula bar will appear, similar to Excel. Name the measure 'Sold Quantities Outside Europe.'
Defining the Expression
Locate the CALCULATE function and double-click to add it. Expressions are crucial in Power BI for retrieving, displaying, grouping, sorting, filtering, and formatting data. In this case, we focus on the total quantity. The total quantity measure is represented by a calculator icon. Double-click it, type a comma to move to the filter section, and specify the continent table's region column.
Applying the Filter
To filter out Europe, use the syntax similar to Excel. Indicate that you want everything except Europe by using the 'not equal to' operator. Enclose 'Europe' in double quotes and close the function with a parenthesis. Press enter, and the new measure 'Sold Quantities Outside Europe' will appear.
Visualizing the New Measure
To visualize the new measure, select a card visual and display 'Sold Quantities Outside Europe.' To format the data, click on the format button, adjust the data label, and switch display units from auto to none to show the whole number. You can also select the currency format you wish to display, such as Euro.
Conclusion
In this tutorial, we learned how to use the CALCULATE function to filter data in Power BI effectively. This function is essential for creating dynamic reports that can adapt to various filtering needs. Future tutorials will explore additional functions to enhance your Power BI skills.

FAQ :

What is the purpose of the Calculate function in Power BI?

The Calculate function is used to modify the context of data evaluation, allowing users to create dynamic calculations based on specific filters or conditions.

How do I create a new measure in Power BI?

To create a new measure, right-click on the desired table in the Fields pane, select 'New measure', and then enter your DAX formula in the formula bar that appears.

Can I filter data in Power BI without using the filter pane?

Yes, you can filter data using DAX functions like Calculate, which allows you to specify conditions directly in your measures.

What is the difference between a measure and a calculated column in Power BI?

A measure is a dynamic calculation that is evaluated based on the context of the report, while a calculated column is a static value that is computed for each row in a table.

How can I format the display of a measure in Power BI?

You can format the display of a measure by selecting the measure in the Fields pane, then using the Format options in the ribbon to adjust settings like data labels and currency.

Quelques cas d'usages :

Sales Reporting

Using the Calculate function to create measures that exclude specific regions, such as Europe, allows sales teams to analyze performance in other markets effectively.

Dynamic Dashboard Creation

By applying the Calculate function, users can create dynamic dashboards that adjust metrics based on user-selected filters, enhancing interactivity and data insights.

Financial Analysis

Financial analysts can use the Calculate function to isolate revenue figures from specific regions or product lines, enabling targeted financial assessments and reporting.

Performance Tracking

Marketing teams can utilize the Calculate function to track campaign performance by excluding certain demographics, allowing for a clearer understanding of target audience engagement.

Inventory Management

Inventory managers can apply the Calculate function to analyze stock levels while excluding certain product categories, helping to optimize inventory control and decision-making.

Glossaire :

Calculate Function

A DAX function in Power BI used to modify the context in which data is evaluated, allowing for dynamic calculations based on specified filters.

DAX

Data Analysis Expressions, a formula language used in Power BI, Excel, and other Microsoft tools for data modeling and analysis.

Measure

A calculation used in Power BI that is evaluated based on the context of the data in the report, often created using DAX.

Filter Pane

A section in Power BI that allows users to apply filters to visuals, controlling which data is displayed based on selected criteria.

Expression

A combination of functions, operators, and values that results in a single value, used in Power BI to control report content and interactivity.

Visual

A graphical representation of data in Power BI, such as tables, charts, or cards, used to convey information effectively.

Card Visual

A type of visual in Power BI that displays a single value prominently, often used for key metrics.

Total Quantity Measure

A specific measure in Power BI that calculates the total quantity of items sold, often used in sales reports.

00:00:03

Calculate is the often used Dex

00:00:06

function in power Bi even though

00:00:09

calculate cannot do anything.

00:00:11

This function works as a base

00:00:14

function. To apply auto dex

00:00:16

functions in different scenarios.

00:00:19

In this report I have added a table

00:00:22

visual that represents the sold

00:00:24

quantities in different continents.

00:00:27

If let's say I wanted to leave

00:00:30

out the quantities from Europe,

00:00:32

I could go to the filters pane

00:00:35

just by selecting the visual.

00:00:37

Here I can select region.

00:00:40

Select all and then uncheck Europe and

00:00:43

just like that the total has been adjusted.

00:00:46

I can only see North America and

00:00:48

South America now to do the same

00:00:51

without using filters we're going

00:00:53

to use a calculate function.

00:00:55

So first thing I'm going to go.

00:00:58

On "sales" right click.

00:01:01

"New measure"

00:01:04

A formula bar will appear just as in Excel.

00:01:09

And then here I'm going to give it and you

00:01:13

name it will be called sold quantities.

00:01:17

Outside Europe.

00:01:24

Now I'm going to locate the

00:01:28

calculate function, double click.

00:01:31

And here I have to add an expression

00:01:35

so expressions are widely used

00:01:38

throughout Power BI report builder

00:01:41

Paginated reports to retrieve,

00:01:43

calculate display.

00:01:44

Group sort filter

00:01:46

parameter and format data.

00:01:47

Many report item properties can be

00:01:50

set to an expression and expressions

00:01:53

basically help you control the content,

00:01:57

design and interactivity of your report .

00:02:01

So In this scenario,

00:02:02

I am focusing on.

00:02:08

The total quantity.

00:02:12

So you see, I've located a total quantity.

00:02:15

Measure is represented by a Calculator.

00:02:18

I double click. I'm going to type.

00:02:21

A comma to go onto my filter and then here

00:02:25

basically I want to filter the

00:02:28

continent table and I'm focusing

00:02:30

on the region column field.

00:02:32

I double click and just as an

00:02:35

Excel to say I want everything

00:02:38

from the continents but Europe.

00:02:40

I'm going to use the syntax

00:02:43

that we use in Excel.

00:02:45

You know like : more than,

00:02:47

less than or equal to.

00:02:49

Here I want to say different,

00:02:52

so it's basically

00:02:54

Less,

00:02:55

And more,

00:02:56

and then I just have to type in

00:02:59

the syntax so you put the quotes,

00:03:02

double quotes and will just type

00:03:04

in Europe and just like that,

00:03:07

I'm going to close this with

00:03:10

another parenthesis.

00:03:11

And press enter.

00:03:15

The new measure appears here as

00:03:17

sold quantity outside Europe,

00:03:19

so I'm going to go pick another visual.

00:03:23

Will use a card.

00:03:25

And then I'll just, lide

00:03:29

Sold quantities outside Europe.

00:03:31

Now remember, if you want to

00:03:34

change the format for this

00:03:37

You can just click on format

00:03:41

button, data label

00:03:42

and display.

00:03:43

Units switched from auto to none and

00:03:46

now you can see the whole number.

00:03:52

And when you do select on the right

00:03:55

sold quantities outside Europe again,

00:03:57

the ribbon lets you display

00:04:00

the currency you wish to

00:04:03

Displace or take euro again.

00:04:08

And there you go.

00:04:10

So the amount appears exactly the same.

00:04:13

And now you know how to

00:04:15

use a calculate function.

00:04:17

We will of course approach other

00:04:20

functions in other tutorials.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

4900 Views

Microsoft Power BI Video Training – Advanced

Dashboard, indicators (KPIs), activity tracking are part of your daily vocabulary? Discover how to quickly glean insights from your data usi...

ALL COURSES