Глава 12. Несвязанные таблицы в Power Pivot

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

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

Несвязанная – таблица, которая добавляется в модель Power Pivot, но намеренно не связывается с другими таблицами. Это может показаться странным: если нет связи, набор фильтров никогда не перетечет в таблицу данных, и несвязанная таблица ничего значимого не добавит в сводную таблицу. Но, не будем торопиться.

Параметризованный отчет

Рассмотрим пример. Сначала покажем результат, а затем объясним, как мы это сделали. Посмотрите на следующую сводную:

Рис. 12.1. Сводная с двумя срезами

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

Рис. 12.2. При изменении курса евро чистые продажи в евро резко упали, в то время как чистые продажи в долларах остались без изменений

Добавление таблицы параметров в модель данных

Мы проигнорируем одну из ранее сделанных рекомендаций и создадим таблицу с помощью копирования и вставки. Так можно сделать, поскольку мы считаем, что эта таблица не будет меняться. Поскольку непосредственно в Power Pivot редактировать ячейки нельзя, создадим простую таблицу в Excel (рис. 12.3а) и вставим ее как новую таблицу в модель данных (рис. 12.3b).

Рис. 12.3. Новая таблица: (а) в Excel, (b) в модели данных Power Pivot

Создадим сводную таблицу и поместим этот столбец в срез:

Рис. 12.4. Сводная с категориями продуктов в строках и курсом евро в срезе

Чаще всего несвязанные таблицы используются в качестве параметров, и эти параметры обычно предоставляются в виде срезов.

В таблице ’Exch Rates’ создадим меру [EURUSD] =MAX(’Exch Rates’[USD per EUR]). Заметьте, что мера создается в таблице параметра, а не данных по продажам.

Рис. 12.5. Мера, которая все время возвращает $1,95

Зачем нам это? Трюк заключается в том, что когда мы делаем выбор на срезе, мера меняется:[1]

Рис. 12.6. Мера возвращает значение, выбранное на срезе!

Перед запуском арифметики (функции MAX) таблица ’Exch Rates’ фильтруется по срезу, который говорит: «[USD per EUR]=$1,45».

Поскольку при выборе пользователем одного значения среза выбирается только одна строка таблицы ’Exch Rates’, мы могли бы использовать MIN(), AVERAGE() или даже SUM() в качестве агрегирующей функции в нашей мере [EURUSD] – все они возвращают один и тот же результат при выборе одного значения. Ваш выбор функции в подобных случаях зависит от личных предпочтений и от того, как вы хотите обрабатывать случаи, когда пользователь выбирает более одного значения. Вы даже можете решить вернуть ошибку, о чем мы расскажем в главе 13.

Рис. 12.7. Excel сообщает, что связи между таблицей Products (откуда берется поле Category) и таблицей ’Exch Rates’ (из которой рассчитывается мера [EURUSD]) нет

Это предупреждение вы будете видеть всякий раз, когда используете несвязанные таблицы. Можете просто закрыть его, чтобы не расходовать место в окне Поля сводной таблицы.

Оценка продаж в EUR по курсу, заданному в срезе

Сама по себе мера [EURUSD] несет мало смысла. Создадим на ее основе новую меру [Net Sales — EUR] = [Net Sales] / [EURUSD]

Рис. 12.8. Новая мера показывает продажи в евро по выбранному курсу!

Мы использовали параметры форматирования в редакторе мер для форматирования новой меры в евро.

Таблица параметров может быть использована не только в срезе, но и в строках/столбцах сводной таблицы. Каждая ячейка меры соответствует одному значению столбца Exch Rate. Это ничем не отличается от использования обычного столбца (который связан с таблицей продаж через связь или находится в таблице продаж) для срезов и строк.

Рис. 12.9. Продажи в евро по категории Accessories за 2004 г. в зависимости от курса евро

Почему важно, чтобы параметрическая таблица были несвязанной? Что бы произошло, если бы таблица ’Exch Rates’ была связана, скажем, с таблицей Sales? Ничего хорошего. В таблице Sales нет столбца, соответствующего значениям в таблице ’Exch Rates’. Мы могли бы добавить вычисляемый столбец, но тогда нам пришлось бы произвольно присвоить строкам транзакций некий обменный курс, что было бы глупостью.[2] И затем, когда пользователь выбирает обменный курс на срезе, это не только повлияет на меру [EURUSD], но и отфильтрует строки из таблицы Sales. В реальной жизни что-то вроде обменного курса полностью отделено от продаж, поэтому нас не должно удивлять, что мы не можем создать значимые связи между ними.

На самом деле концепция несвязанных таблиц добавляет гибкости. Приведем еще один пример.

Несвязанная таблица пороговых значений

В предыдущем примере мы использовали несвязанную таблицу, чтобы ввести числовой параметр продаж в EUR [Net Sales — EUR] и предоставить пользователю отчета контроль над этим параметром. В этом примере пользователю предоставляется контроль над отсечениями (или порогами), с точки зрения, того, какие продукты включать в отчет, а какие нет. Опять же, начнем с желаемого результата:

Рис. 12.10. Сводная с пороговым уровнем $50; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Показано, например, что в категории Clothing (одежда) имеется 20 различных товаров, стоимость каждого из которых 50 долларов США или выше, и на их долю приходится 193 тыс. долларов США продаж.

Откройте Excel-файл ch13_FILTER.xlsx, создайте лист с таблицей порогов (рис. 12.11а), скопируйте таблицу в буфер обмена, перейдите в Power Pivot и создайте новую таблицу MiniListPrice (рис. 12.11b).

Рис. 12.11. Таблица порогов: (а) в Excel, (b) в модели данных Power Pivot

В таблице MinListPrice создайте меру для порога, выбираемого пользователем:

[MinListThreshold] =MAX(MinListPrice[MinListPrice])

Рис. 12.12. Пороговая мера [MinListThreshold], созданная на несвязанной таблице

В отличие от предыдущего примера нам нужен более сложный фильтр, а не один параметр. В прошлый раз мы разделили существующую меру на набор параметров, которым управляли из среза. На этот раз, простая арифметика этого не сделает. Поскольку нам нужно отфильтровать продукты, если они соответствуют нашим критериям, нам нужно использовать функцию CALCULATE(). Она поддерживает оператор >=, поэтому перейдем в редактор мер и создадим еще одну меру (рис. 12.13):

[Products Sales Above Selected List Price] =
CALCULATE([Total Sales]; Products[ListPrice] >= [MinListThreshold])

Рис. 12.13. Мера [Products Sales Above Selected List Price] в редакторе мер

Однако, если нажать кнопку Проверить формулу DAX, появится ошибка:

Рис. 12.14. Ошибка использование функции CALCULATE()

Даже функция CALCULATE() имеет ограничения… Но есть версия приведенной выше формулы, которая работает:

Функция FILTER() является следующая функцией на пути освоения формул DAX. Мы вернемся к этому примеру, но сделаем это в следующей главе, посвященной функции FILTER().

[1] К сожалению, у меня не получилось повторить эти действия за Робом Колли: срез работал некорректно. Поэтому далее в заметке частично используются скриншоты из английской версии книги. Я создал свой модельный файл, имитирующий работу параметрической таблицы курсов валют. В нем срез работал корректно. Видимо проблема в оригинальном файле Роба.  – Прим. Багузина.

[2] Этот конкретный пример с валютными курсами, как раз, допускает такой подход, поскольку можно создать связанную таблицу с обменными курсами на каждый день. Но, поскольку речь об иллюстративном примере, изложение принципов подхода остается полезным. – Прим. Багузина.