Power BI Analytics Benchmark (AB)

Trainings

CALCULATE

CALCULATE Function evaluates a given expression or formula under a set of

defined filters. We Know that in many visualizations, we want to show only a sub set
of data instead of creating visualizations and filtering we will filter the measure
value using CALCULATE Function.

Syntax
CALCULATE (<expression>, <filter1>, <filter2>…)

Examples

SumOfSales = SUM (Orders [Sales])

EastRegionSales = CALCULATE ([SumOfSales], Orders [Region] ="East")

EastCentralSales=
CALCULATE ([SumOfSales], Orders [Region] ="East" || Orders [Region]
="Central")

EastTechSales=
CALCULATE ([SumOfSales], Orders [Region] ="East", Orders [Category]
="Technology")

Between each filter condition it will use AND operation.

ALL

ALL Function returns all the rows in a table, or all the values in a column,
ignoring any filters that might have been applied.

This function is useful for clearing filters and creating calculations on all the
rows in a table or column.

Syntax

ALL ({<table> | <column> [, <column> [, <column> […]]]})

<Table> | <column>

The “table or column” that you want to clear filter on. If you want you can
add multiple columns to clear filter.

www.abtrainings.com
Power BI Analytics Benchmark (AB)
Trainings

Examples

%Sales = [SumOfSales] /CALCULATE ([SumOfSales], ALL (Orders [Category]))

% Sales = [SumOfSales] /CALCULATE ([SumOfSales], ALL (Orders))

Achieving %Sales is a reasonably easy task using simple implicit measures in

Power BI as shown in below.

Drag Category, Sales twice into Visuals. The Visual will give Below Output.

To get %Sales Go to the Field wells and Click on Expand button as shown below.

By Default as shown in below image “Show Value as” Selected as “No

Calculation”, so that it will result Default “Sum of Sales”.

www.abtrainings.com
Power BI Analytics Benchmark (AB)
Trainings

Change “Show Value as” to “Percent of grand total” to get expected % Sales
results

ALLSELECTED

ALLSELECTED will returns all the rows in a table, or all the values in a
column, ignoring any filters that might have been applied inside the query or
Visualization, but keeping filters that come from outside slicer.

%Sales = [SumOfSales]/CALCULATE ([SumOfSales], ALL (Orders))

www.abtrainings.com
Power BI Analytics Benchmark (AB)
Trainings

When You Filter the Visual using the Slicer based on Region Column, you will
see the below results.

%Sales = [SumOfSales]/CALCULATE ([SumOfSales], ALL (Orders))

%SalesAllSelected =
[SumOfSales]/CALCULATE (SUM (Orders [Sales]), ALLSELECTED (Orders))

ALLEXCEPT

Removes all context filters in the table except filters that have been
applied to the specified columns.

www.abtrainings.com
Power BI Analytics Benchmark (AB)
Trainings

1. Calculate Category wise %Sales

%Sales = [SumOfSales]/CALCULATE ([SumOfSales], ALL (Orders))

2. Calculate Category wise %Sales for each Region

%Sales = [SumOfSales]/CALCULATE ([SumOfSales], ALLEXCEPT (Orders, Orders

[Region]))

www.abtrainings.com
Power BI Analytics Benchmark (AB)
Trainings

RELATED

Returns a related value from another table.

The RELATED function requires that a relationship exists between the current table
and the table with related information. You specify the column that contains the
data that you want, and the function follows an existing many-to-one relationship
to fetch the value from the specified column in the related table. If a relationship
does not exist, you must create a relationship.

Syntax

RELATED (<column>)

Example

Person = RELATED(People[Person])

***USERELATIONSHIP

Specifies the relationship to be used in a specific calculation as the one that

exists between columnName1 and columnName2.

Sum of Sales Ship Date = CALCULATE(SUM(Orders[Sales]),

USERELATIONSHIP(Dim_Date[Date], Orders [Ship Date]))

NATURALINNERJOIN

www.abtrainings.com
Power BI Analytics Benchmark (AB)
Trainings

NATURALLEFTOUTERJOIN

CROSSJOIN

CALCULATETABLE

RELATEDTABLE

LOOKUPVALUE

LOOKUPVALUE Function will Returns the value in result_columnName for the

row that meets all criteria specified by search_columnName and search_value.

Syntax

LOOKUPVALUE (<result_columnName>, <search_columnName>, <search_value> [,

<search_columnName>, <search_value>]… [, <alternateResult>])

DName = LOOKUPVALUE (Dept [DNAME], Dept [DEPTNO], Emp [DEPTNO])

The value will return for result_column for the Rows where all pairs of
search_column and search_value have a match.

If there is no match a BLANK will return when You Don’t Supply Alternative Result, if
supplied Alternative Result will be returned.

If multiple rows match the search values and in all cases result_column
values are identical then that value is returned.

However, if result_column returns different values an error or alternateResult,

if supplied, is returned.

To get Related Value using LOOKUPVALUE function we no need to Have Relationship

B/W Tables.

In LOOKUPVALUE function we can write Multiple Conditions

QtySold =
LOOKUPVALUE(Sales[QtySold],Sales[Category],Product[Category],Sales[ProductCod
e],Product[ProductCode])

CALCULATE

CALCULATE Function Evaluates an expression in a context modified by filters.

www.abtrainings.com
Power BI Analytics Benchmark (AB)
Trainings

CALCULATETABLE

Evaluates a table expression in a context modified by filters.

FILTER

Returns a table that has been filtered.

FILTER

The FILTER function is used to return a subset of a table or expression.

CentralOrders = FILTER(Orders,Orders[Region]="Central")

Above Formula will create a New table with only Central Region Orders.

In many cases you can use the CALCULATE function instead of the FILTER
function to produce the same results (the resulting formula is usually easier to
understand, too).

Filter = CALCULATE(SUM(Orders[Sales]), FILTER (orders, Orders[Region]="South" ||

Orders[Segment]="Home Office"))

www.abtrainings.com