Business Data Analytics & Advanced Excel · Intermediate level

Intermediate level · Module 3: DAX for Business Measures · Lesson 2 of 3

CALCULATE and Filter Context

Understand filter context and use CALCULATE to change it for comparisons and shares.

In this lesson you will

  • Explain filter context
  • Use CALCULATE for filtered and share-of-total measures

Key concepts

filter contextCALCULATEALLshare of total

The lesson

Every value in a visual is calculated in a filter context: the branch, month and product selected for that cell.

CALCULATE evaluates a measure in a changed context: Cooking Oil Sales = CALCULATE([Total Sales], Products[Item] = "Cooking oil").

ALL removes filters, which is how shares are calculated: Share of Sales = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Branches))).

Step by step

  1. Explain the filter context of three visual cells.
  2. Write a filtered measure with CALCULATE.
  3. Write a share-of-total measure with ALL.
  4. Check results against Excel.

Practice activity

Add variance, margin and share measures to the solution.

Expected result: Correct measures in every tested context.

Check your understanding

  1. What is filter context?

    Show answer

    The filters that apply when a value is calculated, such as the selected branch and month.

  2. What does ALL do inside CALCULATE?

    Show answer

    Removes filters from a table or column.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.