DAXDAX Trainer

DAX Concept

Calculating Percentage of Year Total Using ALLEXCEPT in Power BI DAX

Learn how to use the ALLEXCEPT function in DAX to dynamically remove all filters from a table except for specified columns, enabling calculations like percentage of year total within a filtered context.

IntermediateALLEXCEPTCALCULATEDIVIDE

Concept Breakdown

The ALLEXCEPT function is a powerful table function that removes all context filters from the specified table except for filters that have been applied to the specified columns. It's often used within CALCULATE to modify the filter context for specific parts of a calculation, allowing for comparisons against totals or other aggregated values at a different granularity (e.g., year total despite month/quarter filters). This approach is more concise and robust than individually removing multiple filters with ALL or REMOVEFILTERS when you want to preserve only a few specific filters.

What You Will Learn

  1. Understand when to use ALLEXCEPT, CALCULATE, DIVIDE 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 `Music Tours - All Except.pbix` file.

  2. Open the `Music Tours - All Except.pbix` file in Power BI Desktop.

  3. Observe the existing matrix visual, which displays a `Count of Shows` for each year, quarter, and month.

  4. Create a new measure. Name it `Percentage of Year Total`.

  5. Using the provided `starter_code` as a guide, complete the DAX formula for `Percentage of Year Total`. The numerator should be the `Count of Shows` in the current filter context, and the denominator should be the `Count of Shows` for the entire `Year`, ignoring `Quarter` and `Month` filters using `ALLEXCEPT(Dates, Dates[Year])`.

  6. Add the new `Percentage of Year Total` measure to the matrix visual.

  7. Format the measure as a percentage with two decimal places.

  8. Verify that the results show percentages correctly, summing up to 100% within each year group.

Starter DAX

Percentage of Year Total =
    DIVIDE(
        COUNT(Shows[ShowID]),
        CALCULATE(
            COUNT(Shows[ShowID]),
            -- Insert ALLEXCEPT function here to remove all filters from the Dates table except the Year filter
        )
    )

Expected Outcome

A matrix visual displaying `Count of Shows` and the newly created `Percentage of Year Total`. For each row within a specific year, the `Percentage of Year Total` should represent the current row's `Count of Shows` relative to that year's total. When summing up all percentages within a given year, the total should be 100%.

Checking your sign-in status...