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

Рубрика: 7. Полезняшки Excel

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

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

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

Ris. 9.1. Svodnaya tablitsa dlya demonstratsii raboty ALL

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

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

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

Ris. 9.2. Poskolku ALL udalil filtr iz MonthNum

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

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

Ris. 9.3. Novaya mera vozvrashhaet vklad kazhdogo mesyatsa v godovoj obem prodazh

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

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

Ris. 9.4. Novaya mera rabotaet dazhe v otsutstvii stolbtsa

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

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

Ris. 9.5. Svodnaya tablitsa so srezom po produktam

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

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

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

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

Ris. 9.6. Na sem otobrannyh produktov prihoditsya 43 vseh chistyh prodazh

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

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

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

Ris. 9.7. Na eti pyat produktov prihoditsya gorazdo bolshaya dolya chistyh prodazh

Рис. 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>; …), где <coli> означает колонку, по которой не изменяют фильтр. Например, ALLEXCEPT(Sales; Sales[ProductKey]) – снять фильтры по всем столбцам таблицы Sales, кроме столбца [ProductKey].

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

Функция ALLSELECTED()

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

Ris. 9.8. Nevernaya baza ne pozvolyaet opredelit vklad kazhdogo mesyatsa v prodazhi

Рис. 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]

Ris. 9.9. Verno vybrannaya baza pozvolyaet uvidet vklad kazhdogo mesyatsa v prodazhi

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

Комментарии: 2 комментария

Ошибка в тексте.
Для рис. 9.6 указана формула: [Selected Products Pct] =[Net Sales] / [All Month Net Sales].
Формула должна быть: [Selected Products Pct] =[Net Sales]/[Net Sales — All Products]

Спасибо. Поправил.


Прокомментировать