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

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

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

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

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

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

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

Ris. 12.1. Svodnaya s dvumya srezami

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

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

Ris. 12.2. Pri izmenenii kursa evro chistye prodazhi v evro rezko upali

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

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

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

Ris. 12.3. Novaya tablitsa

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

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

Ris. 12.4. Svodnaya s kategoriyami produktov v strokah i kursom evro v sreze

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

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

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

Ris. 12.5. Mera kotoraya vse vremya vozvrashhaet 195

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

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

Ris. 12.6. Mera vozvrashhaet znachenie vybrannoe na sreze

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

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

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

Ris. 12.7. Excel soobshhaet chto svyazi net

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

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

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

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

Ris. 12.8. Novaya mera pokazyvaet prodazhi v evro po vybrannomu kursu

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

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

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

Ris. 12.9. Prodazhi v evro po kategorii Accessories za 2004 g. v zavisimosti ot kursa evro

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

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

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

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

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

Ris. 12.10. Svodnaya s porogovym urovnem 50

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

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

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

Ris. 12.11. Tablitsa porogov

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

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

[MinListThreshold] =MAX(MinListPrice[MinListPrice])

Ris. 12.12. Porogovaya mera MinListThreshold sozdannaya na nesvyazannoj tablitse

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

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

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

Ris. 12.13. Mera Products Sales Above Selected List Price v redaktore mer

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

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

Ris. 12.14. Oshibka ispolzovanie funktsii CALCULATE

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

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

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

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

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


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