DAXDAX Trainer

DAX Concept

Applying and Overriding Context Transition in Calculated Columns

Learn to create calculated columns that leverage implicit context transition for row-specific calculations and columns that override context to calculate global totals.

IntermediateSUMXCALCULATEALL

Concept Breakdown

In Power BI, a calculated column operates within a row context. When an aggregation function like SUMX is used within a calculated column, it inherently 'sees' the current row's context. This row context is then implicitly converted into a filter context, allowing the aggregation to filter related tables—this process is known as context transition. To calculate a grand total that ignores this row context (or any existing filter context), functions like CALCULATE combined with modifiers such as ALL or REMOVEFILTERS are essential to override the natural filter behavior.

What You Will Learn

  1. Understand when to use SUMX, CALCULATE, ALL in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a intermediate-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download and unzip the provided Power BI file.

  2. Open the Power BI file and navigate to the 'Product' table.

  3. Create a new calculated column named 'Total sales' that calculates the total sales across the entire database, ensuring it ignores the current product row's context.

  4. Create a second calculated column named 'Sales for product' that calculates the total sales specifically for the product in the current row. This column should leverage context transition implicitly.

  5. Create a third calculated column named 'Product contribution' which calculates the ratio of 'Sales for product' to 'Total sales'.

  6. Verify that your 'Product' table now contains these three new columns with the correct values, matching the provided screenshot.

  7. Save the file as 'Context transition is fun' and close it.

Starter DAX

SUMX( 'TableName', 'TableName'[Column1] * 'TableName'[Column2] )

Expected Outcome

The 'Product' table will have three new columns: 'Total sales' (showing the same grand total value for every product), 'Sales for product' (showing unique sales figures for each product), and 'Product contribution' (a percentage indicating each product's share of total sales).

Checking your sign-in status...