ALLEXCEPT – removes all filter contexts except one (DAX – Power Pivot, Power BI)

The ALLEXCEPT function is used when some calculation should ignore all filter contexts, except one.

Its logic is similar to ALL. But when ALL “ignores” all filters (when whole table is used as an argument) or selected column(s) (when column(s) is(are) used as an argument, ALLEXCEPT ignores everything except what is in argument.

Little bit strange, is it? So how does it work in example?

For example, here we calculate total price for cars, by Brand, Model and Color (columns in one table).

In the calculation we need for each row the percent from total Brand. For example, for blue Alfa Romeo 147 we´d like to know, how many percent makes its price from total Alfa Romeo prices (second column), which means to divide 7 400 by 178 400.

The total value for Brand can be calculated as “calculate the total price, but ignore filtering by everything except of Brand”.

In DAX it means:

• Total price for Brand = CALCULATE(
     SUM(Sheet1[Price]),
     ALLEXCEPT(Sheet1,Sheet1[Brand]))

and the total percent is:

• Percent of Brand total price = DIVIDE(
     SUM(Sheet1[Price]),
     CALCULATE(
        SUM(Sheet1[Price]),
        ALLEXCEPT(Sheet1,Sheet1[Brand])
     )
  )

Yes, the same result can be obtained with this, but the syntax is more complicated:

• Percent of Brand total price = DIVIDE(
     SUM(Sheet1[Price]),
     CALCULATE(
        SUM(Sheet1[Price]),
        ALL(Sheet1[Color],Sheet1[Model])
     )
  )