CALCULATE in Depth

// ALL(table_or_column)
// Removes ALL filters from a table or a specific column
Grand Total =
    CALCULATE(SUM(Orders[Revenue]), ALL(Orders))
// Ignores every filter — slicer, visual, page — returns 4,115 always

All Regions Revenue =
    CALCULATE(SUM(Orders[Revenue]), ALL(Orders[Region]))
// Removes Region filter only — other filters (Tier, Status) still apply

// ALLSELECTED(table_or_column)
// Removes visual-level row/column filters but KEEPS slicer filters
// The "% of total within slicer" workhorse

Revenue % of Total =
    DIVIDE(
        SUM(Orders[Revenue]),
        CALCULATE(SUM(Orders[Revenue]), ALLSELECTED(Orders))
    )

// ALLEXCEPT(table, column_to_keep1, column_to_keep2, ...)
// Removes all filters EXCEPT from the specified columns
// Useful when you want to aggregate across one dimension
// while keeping all other filters intact

Revenue Ignoring Status =
    CALCULATE(
        SUM(Orders[Revenue]),
        ALLEXCEPT(Orders, Orders[Region], Orders[CustomerTier])
        -- Removes filters from ALL columns EXCEPT Region and Tier
        -- Status filter is removed; Region and Tier filters stay
    )

// KEEPFILTERS(filter_expression)
// Adds a filter WITHOUT replacing an existing one on the same column
// Result = intersection of the existing filter AND the new filter

North Revenue (KEEPFILTERS) =
    CALCULATE(
        SUM(Orders[Revenue]),
        KEEPFILTERS(Orders[Region] = "North")
    )
// Normal CALCULATE with Region="North":
//   Replaces any existing Region filter → always returns North revenue
// KEEPFILTERS with Region="North":
//   Intersects with existing Region filter
//   When visual row = South: South ∩ North = empty → BLANK
//   When visual row = North: North ∩ North = North → 1,630
//   When no row filter: no existing filter ∩ North = North → 1,630

// REMOVEFILTERS(table_or_column) — explicit alias for ALL()
// Clearer intent in some scenarios — "I am removing filters" vs "ALL"
Revenue No Region Filter =
    CALCULATE(
        SUM(Orders[Revenue]),
        REMOVEFILTERS(Orders[Region])
    )
// Identical to: CALCULATE(SUM(Orders[Revenue]), ALL(Orders[Region]))

// When to use FILTER() instead of column = value

// Simple condition — use direct column filter (faster)
North Revenue = CALCULATE(SUM(Orders[Revenue]), Orders[Region] = "North")

// Condition involving a measure — MUST use FILTER()
// (measures cannot be used directly as CALCULATE filter arguments)
High Value Revenue =
    CALCULATE(
        SUM(Orders[Revenue]),
        FILTER(
            Orders,
            Orders[Revenue] > AVERAGE(Orders[Revenue])
        )
    )
// FILTER iterates every row of Orders, keeps rows where
// Revenue > average Revenue. Returns those rows as a table.
// CALCULATE then evaluates SUM only over those filtered rows.

// Pattern 1 — Conditional total (fixed segment regardless of context)
Premium Revenue =
    CALCULATE(SUM(Orders[Revenue]), Orders[CustomerTier] = "Premium")
// Returns Premium revenue in every cell — ignores Tier slicer

// Pattern 2 — Prior period comparison using VAR for clarity
Revenue vs Prior Month =
VAR CurrentRev = SUM(Orders[Revenue])
VAR PriorRev   =
    CALCULATE(
        SUM(Orders[Revenue]),
        DATEADD(Calendar[Date], -1, MONTH)
    )
RETURN
    CurrentRev - PriorRev

// Pattern 3 — Multiple filter arguments (AND logic)
North Premium Revenue =
    CALCULATE(
        SUM(Orders[Revenue]),
        Orders[Region] = "North",
        Orders[CustomerTier] = "Premium"
    )

// Pattern 4 — OR logic using FILTER (two values on same column)
Late or Processing Revenue =
    CALCULATE(
        SUM(Orders[Revenue]),
        FILTER(
            ALL(Orders[Status]),
            Orders[Status] = "Late" || Orders[Status] = "Processing"
        )
    )
// Note: multiple CALCULATE arguments = AND
// For OR on the same column, use FILTER() with || operator
// Alternative using IN operator:
Late or Processing Revenue =
    CALCULATE(
        SUM(Orders[Revenue]),
        Orders[Status] IN {"Late", "Processing"}
    )