Глава 9. DAX-функция ALL() для снятия фильтров

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава       Содержание    Следующая глава

Функция ALL() используется в качестве одного из аргументов [Filter] в функции CALCULATE() для снятия фильтра. Рассмотрим следующую сводную таблицу: [Net Sales] отображаются в разрезе месяцев по году, выбранному на срезе:

Рис. 9.1. Сводная таблица для демонстрации работы ALL()

Скачать заметку в формате Word или pdf, примеры в формате Excel

Введем новую меру: [All Month Net Sales] =CALCULATE([Net Sales]; ALL(Sales[MonthNum]). В этой мере фильтр с [MonthNum] снят, поэтому для любого месяца значение будет одним и тем же:

Рис. 9.2. Поскольку ALL() удалил фильтр из [MonthNum], каждая ячейка меры в правом столбце имеет точно такой же набор фильтров, как и общий итог в левом столбце

Добавим еще одну меру – доля месячных продаж – [Pct of All Month Net Sales] =[Net Sales]/[All Month Net Sales]:

Рис. 9.3. Новая мера возвращает вклад каждого месяца в годовой объем продаж; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Вы можете не отражать меру [All Month Net Sales] в сводной таблице. При этом мера [Pct of All Month Net Sales] по-прежнему работает:

Рис. 9.4. Новая мера работает даже в отсутствии столбца [All Month Net Sales]; это еще раз подчеркивает, что мера рассчитывается на основе исходной таблицы в Power Pivot, а не на основе полей сводной таблицы

В следующем примере добавим срез по [ProductKey]:

Рис. 9.5. Сводная таблица со срезом по продуктам

Теперь добавим две меры, которые игнорируют любые фильтры по [ProductKey]:

[Net Sales — All Products] =CALCULATE([Net Sales]; ALL(Sales[ProductKey]))

[Selected Products Pct] =[Net Sales] / [Net Sales — All Products]

Рис. 9.6. На семь отобранных продуктов приходится 4,3% всех чистых продаж в апреле 2003 года, но лишь 0,1% всех продаж в июле 2003 года.

Мы применили условное форматирование для большей наглядности представленных результатов.

Поменяем выбор артикулов в срезе:

Рис. 9.7. На эти пять артикулов приходится гораздо большая доля чистых продаж, чем на предыдущие семь. Обратите внимание, что выделенный средний столбец (мера ALL) не изменился по сравнению с предыдущим рисунком.

Функция ALL() может использоваться с аргументами, отличными от одного столбца:

ALL(<Col1>; <Col2>; …) – можно перечислить несколько столбцов, например, ALL(Sales[ProductKey]; Sales[Year])

ALL(<TableName>) – снимет фильтры во всех столбцах таблицы, например, ALL(Sales)

Функция ALLEXCEPT()

Предположим, у вас есть 12 столбцов в таблице, и вы хотите применить ALL() к 11 из 12. Синтаксис функции ALLEXCEPT(<Table>; <col1>; <col2>; …), где <col_i> означает колонку, по которой не изменяют фильтр. Например, ALLEXCEPT(Sales; Sales[ProductKey]) – снять фильтры по всем столбцам таблицы Sales, кроме столбца [ProductKey].

Помимо удобства, функция ALLEXCEPT() позволяет не изменять формулу, если впоследствии вы добавите новый столбец в таблицу. Функция ALL(<перечислить каждый столбец>) не будет применяться к новому столбцу, пока вы не измените формулу.

Функция ALLSELECTED()

Вернемся к рис. 9.3, на котором показан вклад каждого месяца в годовые продажи. В качестве базы отлично подошли годовые продажи, выраженные мерой [All Month Net Sales]. Но что, если вас заинтересует вклад месяцев в квартальные, полугодовые или иным образом выбранные временные интервалы!? Рассчитанные ранее меры не справятся с этой задачей:

Рис. 9.8. Неверная база не позволяет определить вклад каждого месяца в продажи первого полугодия или второго квартала

Проблема в том, что мера [All Month Net Sales] перестала быль адекватной базой, ведь в первом случае выбрано полугодие (а не весь год), а во втором – квартал. Вот для этих целей и служит функция ALLSELECTED(), отбирающая в качестве базы только все выбранные месяцы. Давайте определим две новые меры:

[Net Sales for All Selected Months] =CALCULATE([Net Sales]; ALLSELECTED(Sales[MonthNum]))

[Pct of All Selected Months Net Sales] =[Net Sales] / [Net Sales for All Selected Months]

Рис. 9.9. Верно выбранная база позволяет увидеть вклад каждого месяца в продажи