Webbo3 Power BI · Cohort 1 · Day 10 of 16

Day 10: CALCULATE

The most important function in DAX: working out a measure under a filter you choose, instead of only the one the reader clicked.

On Day 8 a measure followed the filters: pick Lekki and Total Revenue showed Lekki. Today you learn to set the filter yourself, inside the measure.

Open your Day 9 file. You need the four relationships and the Total Revenue measure; every example today builds on [Total Revenue].

1. What CALCULATE Is

Definition

CALCULATE works out a measure after changing the filters. You give it the measure first, then one or more filters. It applies your filters, keeps the reader's filters on everything else, and then calculates.

Real-Life Example

You ask the accountant for "total revenue" and she adds up whatever pile is on her desk. With CALCULATE you hand her a note with the question: "total revenue, but only South West stations". She does not need you to move the piles first. The note tells her which pile to use.

How It Is Written

The first argument is what to calculate, usually a measure you already have. Every argument after it is a filter. The simplest filter is a column compared with a value: Station_Master[Region] = "South West".

1
2
3
4
5
6

// Pattern: CALCULATE ( what to calculate, filter, filter, ... )
Revenue South West =
CALCULATE (
    [Total Revenue],                  // what to calculate
    Station_Master[Region] = "South West"  // the filter to apply first
)

South West is 43% of all revenue. The new measure did not need a slicer or a filter pane: the filter lives inside it.

2. The Kinds of Filter You Can Give It

Types

Type 1: One Condition

A column, a comparison and a value. Text goes in double quotes, numbers do not. The comparisons are the ones you know from Excel: =, <> (not equal), >, <, >= and <=.

1
2
3
4
5
6

// Only rows whose product is in the Fuel category
Fuel Revenue =
CALCULATE (
    [Total Revenue],
    Product_Master[Category] = "Fuel"
)

The filter is on the Products table, not on Sales. It still works because Products filters Sales through the relationship you built on Day 7. CALCULATE filters the model, and the model does the rest.

Type 2: Two Conditions (AND)

Separate the filters with commas. A row has to pass every one of them.

1
2
3
4
5
6
7

// Both must be true: South West station AND a fuel product
South West Fuel Revenue =
CALCULATE (
    [Total Revenue],
    Station_Master[Region] = "South West",   // condition 1
    Product_Master[Category] = "Fuel"        // condition 2
)

Type 3: A List of Values (OR)

IN followed by a list in curly brackets means "any of these". It is the tidy way to say "North Central or North West" on one column.

1
2
3
4
5
6

// Either region counts
Northern Revenue =
CALCULATE (
    [Total Revenue],
    Station_Master[Region] IN { "North Central", "North West" }
)

Type 4: Removing a Filter with ALL

ALL ( Station_Master ) used as a filter means "ignore every filter on the Station_Master table". REMOVEFILTERS ( Station_Master ) does exactly the same and says so more plainly; you will see both in other people's reports. Section 3 shows why you would want this.

3. What CALCULATE Does to Each Row of a Visual

The Rule

This is the part that confuses everybody, so read it twice. A table visual filters each of its rows. CALCULATE then applies its own filters, and:

• If CALCULATE filters the same column the row is filtering, CALCULATE replaces the row's filter with its own.
• If CALCULATE filters a different column, both filters apply, and only rows that pass both are counted.

Real-Life Example

The accountant has a pile on her desk labelled "North Central". Your note says "only South West". Same label, so she swaps the pile. Now the pile is labelled "Wuse Filling Station" and your note still says "only South West". Different labels, so she keeps the pile and checks it against the note: Wuse is not South West, so the answer is nothing.

Every row says ₦62,094,472. The row filter was on Station_Master[Region] and so was CALCULATE's, so CALCULATE replaced it. The measure is doing exactly what it was told, and it does not answer "revenue per region". Total Revenue does that.

Now both filters apply. Ikeja, Lekki and Bodija are in the South West, so they pass both and show their own revenue. The other four fail CALCULATE's filter and show blank (Power BI shows an empty cell). The total is South West's total.

1
2
3
4
5
6

// Ignore every filter on the Station_Master table, then add up
All Stations Revenue =
CALCULATE (
    [Total Revenue],
    ALL ( Station_Master )
)

Every row shows the grand total, because ALL removed the row's station filter. On its own that looks useless. Divide one by the other and you get each station's share of the business, which is the first thing you build in tomorrow's lab.

4. Common Mistakes

Mistake 1: A Typo in the Value

DAX does not check that "Southwest" is a real region. It filters to rows where the region is "Southwest", finds none, and shows blank. No error, just an empty card. When a CALCULATE measure is blank, copy the value straight from the column in Table view.

Capital letters do not matter: "south west" matches "South West", because DAX compares text without caring about case. Spaces and hyphens do matter.

Mistake 2: Filtering on a Measure

A simple filter must compare a column with a value. [Total Revenue] > 1000000 is a measure, so Power BI refuses the formula and underlines it in red. Filtering by a measure needs the FILTER function, which you will not need in this course.

Mistake 3: Expecting a Per-Row Answer from a Replaced Filter

Section 3's first table. If a CALCULATE measure shows the same number on every row, check whether its filter is on the same column as the rows. That is almost always the reason.

5. Putting It Together

A manager asks: "How much of our fuel revenue comes from the South West, as a share of all fuel?" Three measures you now know answer it, each building on the one before.

1
2
3
4
5
6
7
8
9
10
11

// Step 1: fuel revenue, all regions
Fuel Revenue =
CALCULATE ( [Total Revenue], Product_Master[Category] = "Fuel" )

// Step 2: fuel revenue, South West only
South West Fuel Revenue =
CALCULATE ( [Fuel Revenue], Station_Master[Region] = "South West" )

// Step 3: one divided by the other, safely
South West Share of Fuel =
DIVIDE ( [South West Fuel Revenue], [Fuel Revenue] )

Step 2 uses CALCULATE on a measure that already contains CALCULATE. That is normal: the Fuel filter comes with [Fuel Revenue], and the Region filter is added on top. Format the share as a percentage (Measure tools, Format, Percentage).

1
2
3
4
5
6

// Day 8's count, with the unmanned sales left out
People Selling =
CALCULATE (
    DISTINCTCOUNT ( Sales[AttendantName] ),
    Sales[AttendantName] <> "Unmanned"
)

CALCULATE works on any expression, not only [Total Revenue]. Here it wraps a DISTINCTCOUNT, and the filter uses <>, "is not equal to".

Do This Now

1. Write Revenue South West and check it against the card above.
2. Write Fuel Revenue, South West Fuel Revenue and Northern Revenue.
3. Build both tables from section 3 and explain each one out loud to yourself.
4. Write All Stations Revenue and put it beside Total Revenue by station.
5. Build the three-step South West Share of Fuel and format it as a percentage.
6. Do the Try This.

Checkpoint

Next, Day 11 Lab: CALCULATE together. Share of total, year against year, and the measures a manager actually asks for. The Solo Project is released.