Моделирование данных с DAX — Обзор

Лица, принимающие решения во всех организациях, выявили необходимость анализа исторических данных их организации в целом и отрасли в целом. Это становится критически важным изо дня в день в современном конкурентном мире, чтобы соответствовать постоянно меняющимся бизнес-задачам.

Большие данные и бизнес-аналитика стали модными словами в мире бизнеса. Источники данных стали огромными, а форматы данных стали вариантами. Потребность в часе состоит в том, чтобы иметь простые в использовании инструменты для обработки постоянно меняющихся огромных данных за меньшее время, чтобы получить представление и принять соответствующие решения в соответствующее время.

Аналитики данных больше не могут ждать обработки необходимых данных ИТ-отделом. Им требуется удобный инструмент, который позволяет им быстро понять необходимые данные и сделать их доступными в формате, который помогает лицам, принимающим решения, предпринять необходимые действия в нужное время.

В Microsoft Excel имеется мощный инструмент Power Pivot, который был доступен в качестве надстройки в предыдущих версиях Excel и является встроенной функцией в Excel 2016. База данных Power Pivot, называемая моделью данных и языком формул, который работает в модели данных, которая называется DAX ( D ata A nalysis E xpressions), позволяет пользователю Excel мгновенно выполнять такие задачи, как моделирование и анализ данных.

В этом руководстве вы изучите моделирование и анализ данных с использованием DAX на основе модели данных Power Pivot. Образец базы данных Profit and Analysis используется для иллюстраций в этом руководстве.

Концепции моделирования и анализа данных

Данные, которые вы получаете из различных источников, называемых необработанными данными, нуждаются в обработке, прежде чем вы сможете использовать их для целей анализа. Вы узнаете об этом в главе — Концепции моделирования и анализа данных.

Моделирование и анализ данных с помощью Excel Power Pivot

Поскольку инструментом, который вы освоите в этом руководстве, является Excel Power Pivot, вам необходимо знать, как выполняются этапы моделирования и анализа данных в Power Pivot. Вы изучите их на более широком уровне в главе — Моделирование и анализ данных с помощью Excel Power Pivot.

Переходя к следующим главам, вы узнаете о различных аспектах функций Power Pivot, DAX и DAX при моделировании и анализе данных.

К концу учебника вы сможете выполнять моделирование и анализ данных с помощью DAX для любого имеющегося контекста.

Моделирование данных с помощью DAX — Основные понятия

Бизнес-аналитика (BI) приобретает все большее значение в нескольких областях и организациях. Принятие решений и прогнозирование на основе исторических данных стали критически важными в постоянно растущем конкурентном мире. Существует огромный объем данных, доступных как изнутри, так и извне из разнообразных источников для любого типа анализа данных.

Однако задача состоит в том, чтобы извлечь соответствующие данные из доступных больших данных в соответствии с текущими требованиями и сохранить их так, чтобы это было удобно для проецирования различных данных из данных. Модель данных, полученная таким образом с использованием ключевых бизнес-терминов, является ценным средством коммуникации. Модель данных также должна обеспечивать быстрый способ создания отчетов по мере необходимости.

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

Предпосылки для модели данных для BI

Модель данных для BI должна соответствовать требованиям бизнеса, для которого проводится анализ данных. Ниже приведены минимальные основы, которым должна соответствовать любая модель данных:

Модель данных должна быть специфичной для бизнеса

Модель данных, которая подходит для одной сферы деятельности, может не подходить для другой сферы деятельности. Следовательно, модель данных должна быть разработана на основе конкретного бизнеса, используемых бизнес-терминов, типов данных и их взаимосвязей. Он должен основываться на целях и типах решений, принятых в организации.

Модель данных должна иметь встроенный интеллект

Модель данных должна включать встроенные аналитические данные с помощью метаданных, иерархий и наследований, которые облегчают эффективный и эффективный процесс бизнес-аналитики. Благодаря этому вы сможете предоставить общую платформу для разных пользователей, исключая повторение процесса.

Модель данных должна быть надежной

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

Модель данных должна быть масштабируемой

Модель данных должна быть способна быстро и эффективно приспосабливаться к изменяющимся бизнес-сценариям. Новые данные или новые типы данных, возможно, должны быть включены. Обновления данных, возможно, придется эффективно обрабатывать.

Моделирование данных для BI

Моделирование данных для BI состоит из следующих этапов —

• Формирование данных
• Загрузка данных
• Определение отношений между таблицами
• Определение типов данных
• Создание новых данных

Формирование данных

Данные, необходимые для построения модели данных, могут быть из разных источников и могут быть в разных форматах. Вам необходимо определить, какая часть данных из каждого из этих источников данных требуется для конкретного анализа данных. Это называется формирование данных.

Например, если вы извлекаете данные о всех сотрудниках организации, вам необходимо решить, какие сведения о каждом сотруднике соответствуют текущему контексту. Другими словами, вам необходимо определить, какие столбцы таблицы сотрудников необходимо импортировать. Это связано с тем, что чем меньше число столбцов в таблице в модели данных, тем быстрее будут выполняться вычисления в таблице.

Загрузка данных

Вам необходимо загрузить идентифицированные данные — таблицы данных с выбранными столбцами в каждой из таблиц.

Определение отношений между таблицами

Далее необходимо определить логические отношения между различными таблицами, которые облегчают объединение данных из этих таблиц, т. Е. Если у вас есть таблица — Продукты — содержащая данные о продуктах, и таблица — Продажи — с различными транзакциями продаж продуктов, Определяя отношения между двумя таблицами, вы можете суммировать продажи в зависимости от продукта.

Определение типов данных

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

Создание новых данных Insights

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

Новые данные могут быть представлены в виде метаданных, которые могут быть легко поняты и использованы конкретными деловыми людьми.

Анализ данных

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

Моделирование данных с помощью Excel Power Pivot

Microsoft Excel Power Pivot — отличный инструмент для моделирования и анализа данных.

• Модель данных — это база данных Power Pivot.

• DAX — это язык формул, который можно использовать для создания метаданных с данными в модели данных с помощью формул DAX.

• Мощные сводные таблицы в Excel, созданные с данными и метаданными в модели данных, позволяют анализировать данные и представлять результаты.

Модель данных — это база данных Power Pivot.

DAX — это язык формул, который можно использовать для создания метаданных с данными в модели данных с помощью формул DAX.

Мощные сводные таблицы в Excel, созданные с данными и метаданными в модели данных, позволяют анализировать данные и представлять результаты.

В этом руководстве вы изучите моделирование данных с помощью модели данных Power Pivot и DAX, а также анализ данных с помощью Power Pivot. Если вы новичок в Power Pivot, обратитесь к учебному пособию по Excel Power Pivot.

Вы изучили шаги процесса моделирования данных в предыдущей главе — Концепции моделирования и анализа данных. В этой главе вы узнаете, как выполнить каждый из этих шагов с помощью модели данных Power Pivot и DAX.

В следующих разделах вы узнаете каждый из этих этапов процесса применительно к модели данных Power Pivot и о том, как используется DAX.

Формирование данных

В Excel Power Pivot вы можете импортировать данные из различных типов источников данных, а во время импорта вы можете просматривать и выбирать таблицы и столбцы, которые вы хотите импортировать.

• Определите источники данных.

• Найдите типы источников данных. Например, база данных или служба данных или любой другой источник данных.

• Определите, какие данные актуальны в текущем контексте.

• Определите подходящие типы данных для данных. В модели данных Power Pivot у вас может быть только один тип данных для всего столбца в таблице.

• Определите, какие из таблиц являются таблицами фактов, а какие — таблицами измерений.

• Определите соответствующие логические отношения между таблицами.

Определите источники данных.

Найдите типы источников данных. Например, база данных или служба данных или любой другой источник данных.

Определите, какие данные актуальны в текущем контексте.

Определите подходящие типы данных для данных. В модели данных Power Pivot у вас может быть только один тип данных для всего столбца в таблице.

Определите, какие из таблиц являются таблицами фактов, а какие — таблицами измерений.

Определите соответствующие логические отношения между таблицами.

Загрузка данных в модель данных

Вы можете загрузить данные в модель данных с помощью нескольких параметров, представленных в окне Power Pivot на ленте. Вы можете найти эти опции в группе «Получить внешние данные».

Вы узнаете, как загрузить данные из базы данных Access в модель данных, в главе «Загрузка данных в модель данных».

Для наглядности используется база данных Access с данными о прибылях и убытках.

Определение типов данных в модели данных

Следующим шагом в процессе моделирования данных в Power Pivot является определение типов данных столбцов в таблицах, которые загружаются в модель данных.

Вы узнаете, как определять типы данных столбцов в таблицах в главе «Определение типов данных в модели данных».

Создание отношений между таблицами

Следующим шагом в процессе моделирования данных в Power Pivot является создание связей между таблицами в модели данных.

Вы узнаете, как создавать связи между таблицами в главе «Расширение модели данных».

Создание новых данных Insights

В модели данных вы можете создавать метаданные, необходимые для создания новых данных, путем —

• Создание вычисляемых столбцов
• Создание таблицы дат
• Создание мер

Затем вы можете проанализировать данные, создав динамические сводные таблицы Power, основанные на столбцах таблиц и показателях, которые отображаются в виде полей в списке полей сводной таблицы.

Добавление вычисляемых столбцов

Вычисляемые столбцы в таблице — это столбцы, добавляемые в таблицу с помощью формул DAX.

Вы узнаете, как добавить вычисляемые столбцы в таблицу в модели данных, в главе «Расширение модели данных».

Создание таблицы дат

Чтобы использовать функции Time Intelligence в формулах DAX для создания метаданных, вам требуется таблица Date. Если вы новичок в таблицах дат, обратитесь к главе «Общие сведения о таблицах дат».

Вы узнаете, как создать таблицу Date в модели данных, в главе «Расширение модели данных».

Создание мер

Вы можете создавать различные показатели в таблице данных, используя функции DAX и формулы DAX для различных вычислений, как это требуется для анализа данных в текущем контексте.

Это важный шаг моделирования данных с помощью DAX.

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

Анализ данных с помощью Power PivotTables

Вы можете создать Power PivotTable для каждого из аспектов анализа прибылей и убытков. Изучая, как создавать показатели с использованием DAX в последующих главах, вы также узнаете, как анализировать данные с помощью этих показателей с помощью Power PivotTables.

Загрузка данных в модель данных

Вы можете загрузить данные из разных типов источников данных в модель данных. Для этого вы можете найти различные параметры в группе «Получить внешние данные» на ленте в окне Power Pivot.

Как вы можете заметить, вы можете загружать данные из баз данных или из служб данных или нескольких других типов источников данных.

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

Инициирование с новой моделью данных

В этом разделе вы узнаете, как моделировать данные для анализа прибылей и убытков. Данные для анализа находятся в базе данных Microsoft Access.

Вы можете инициировать новую модель данных следующим образом:

• Откройте новую книгу Excel
• Перейдите на вкладку PowerPivot на ленте.
• Нажмите Управление в группе Модель данных

Откроется окно Power Pivot. Окно будет пустым, так как вы еще не загрузили данные.

Загрузка данных из базы данных Access в модель данных

Чтобы загрузить данные из базы данных Access, выполните следующие действия:

• Нажмите «Из базы данных» в группе «Получить внешние данные» на ленте.
• Нажмите From Access в выпадающем списке.

Откроется диалоговое окно мастера импорта таблиц.

• Перейдите к файлу доступа.

• Дайте понятное имя для связи.

Перейдите к файлу доступа.

Дайте понятное имя для связи.

• Нажмите кнопку Далее. Появится следующая часть мастера импорта таблиц.

• В мастере импорта таблиц выберите параметр «Выбрать» из списка таблиц и представлений, чтобы выбрать данные для импорта.

Нажмите кнопку Далее. Появится следующая часть мастера импорта таблиц.

В мастере импорта таблиц выберите параметр «Выбрать» из списка таблиц и представлений, чтобы выбрать данные для импорта.

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

• Выберите все таблицы.

• Дайте дружеские имена столам. Это необходимо, поскольку эти имена появляются в сводных таблицах Power и, следовательно, должны быть понятны всем.

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

Выберите все таблицы.

Дайте дружеские имена столам. Это необходимо, поскольку эти имена появляются в сводных таблицах Power и, следовательно, должны быть понятны всем.

Выбор столбцов в таблицах

Возможно, вам не понадобятся все столбцы в выбранных таблицах для текущего анализа. Следовательно, вам нужно выбрать только те столбцы, которые вы выбрали при формировании данных.

• Нажмите кнопку «Просмотр и фильтр». Появится следующая часть мастера импорта таблиц — Просмотр выбранной таблицы.

Нажмите кнопку «Просмотр и фильтр». Появится следующая часть мастера импорта таблиц — Просмотр выбранной таблицы.

• Как видно на скриншоте выше, заголовки столбцов имеют флажки. Выберите столбцы, которые вы хотите импортировать в выбранную таблицу.

• Нажмите ОК. Повторите то же самое для других таблиц.

Как видно на скриншоте выше, заголовки столбцов имеют флажки. Выберите столбцы, которые вы хотите импортировать в выбранную таблицу.

Нажмите ОК. Повторите то же самое для других таблиц.

Импорт данных в модель данных

Вы находитесь на последнем этапе загрузки данных в модель данных. Нажмите кнопку Готово в мастере импорта таблиц. Появится следующая часть мастера импорта таблиц.

Статус импорта будет отображен. Статус наконец показывает Успех, когда загрузка данных завершена.

Просмотр данных в модели данных

Импортированные таблицы появятся в окне Power Pivot. Это вид модели данных

Вы можете наблюдать следующее —

• Каждая из таблиц отображается на отдельной вкладке.
• Имена вкладок — это имена соответствующих таблиц.
• Область под данными предназначена для расчетов.

Просмотр имени соединения

Нажмите Существующие подключения в группе Получить внешние данные. Появится диалоговое окно «Существующие подключения», как показано на следующем снимке экрана.

Как видно на снимке экрана выше, указанное имя подключения отображается в разделе «Подключения данных PowerPivot».

Определение типов данных в модели данных

В модели данных Power Pivot все данные в столбце должны быть одного типа данных. Чтобы выполнить точные вычисления, необходимо убедиться, что тип данных каждого столбца в каждой таблице в модели данных соответствует требованиям.

Таблицы в модели данных

В модели данных, созданной в предыдущей главе, есть 3 таблицы:

• Счета
• География Локн
• Финансовые данные

Обеспечение подходящих типов данных

Чтобы убедиться, что столбцы в таблицах соответствуют требованиям, необходимо проверить их типы данных в окне Power Pivot.

• Нажмите на столбец в таблице.

• Обратите внимание на тип данных столбца, отображаемый на ленте в группе «Форматирование».

Нажмите на столбец в таблице.

Обратите внимание на тип данных столбца, отображаемый на ленте в группе «Форматирование».

Если тип данных выбранного столбца не подходит, измените тип данных следующим образом.

• Нажмите стрелку вниз рядом с типом данных в группе Форматирование.

• Выберите соответствующий тип данных в раскрывающемся списке.

• Повторите эти действия для каждого столбца во всех таблицах в модели данных.

Нажмите стрелку вниз рядом с типом данных в группе Форматирование.

Выберите соответствующий тип данных в раскрывающемся списке.

Повторите эти действия для каждого столбца во всех таблицах в модели данных.

Столбцы в таблице счетов

В таблице Accounts у вас есть следующие столбцы —

Sr.No	Колонка и описание
1	учетная запись Содержит один номер счета для каждой строки. Столбец имеет уникальные значения и используется при определении взаимосвязи с таблицей финансовых данных.
2	Учебный класс Класс, связанный с каждой учетной записью. Пример — расходы, чистый доход и т. Д.
3	Подкласс Описывает тип расходов или доходов. Пример — Люди.

Содержит один номер счета для каждой строки. Столбец имеет уникальные значения и используется при определении взаимосвязи с таблицей финансовых данных.

Класс, связанный с каждой учетной записью. Пример — расходы, чистый доход и т. Д.

Описывает тип расходов или доходов. Пример — Люди.

Все столбцы в таблице «Счета» носят описательный характер и, следовательно, имеют текстовый тип данных.

Столбцы в таблице географических локаций

Таблица Geography Locn содержит данные о каждом Центре прибыли.

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

Столбцы в таблице финансовых данных

В таблице данных о финансах у вас есть следующие столбцы —

колонка	Описание	Тип данных
Финансовый месяц	Месяц и год	Текст
Центр прибыли	Фирменный стиль	Текст
учетная запись	Номер счета. Каждая учетная запись может иметь несколько Центров прибыли.	Текст
бюджет	Ежемесячные суммы бюджета для каждого Центра прибыли.	валюта
фактический	Ежемесячные фактические суммы для каждого Центра прибыли.	валюта
Прогноз	Ежемесячный прогноз суммы для каждого центра прибыли.	валюта
Актуальные люди	Конец месяца — фактическое количество сотрудников для каждого Центра прибыли каждой учетной записи.	Целое число
Бюджетные люди	Количество сотрудников на конец месяца в бюджете для каждого Центра прибыли каждой учетной записи.	Целое число
Прогноз Люди	Прогноз числа сотрудников на конец месяца для каждого Центра прибыли каждого Счета.	Целое число

Номер счета.

Каждая учетная запись может иметь несколько Центров прибыли.

Типы таблиц в модели данных

Таблицы Accounts и Geography Locn — это таблицы измерений, также называемые справочными таблицами .

Таблица финансовых данных — это таблица фактов, также известная как таблица данных. Таблица финансовых данных содержит данные, необходимые для расчета прибыли и анализа. Вы также создадите метаданные в форме показателей и вычисляемых столбцов в этой таблице «Финансовые данные», чтобы моделировать данные для различных типов расчетов прибылей и убытков в ходе работы с этим учебным пособием.

Понимание таблиц данных

Анализ данных включает в себя просмотр данных с течением времени и проведение расчетов за периоды времени. Например, вам, возможно, придется сравнить прибыль текущего года с прибылью предыдущего года. Точно так же вам, возможно, придется прогнозировать рост и прибыль в ближайшие годы. Для этого вам необходимо использовать группирование и агрегирование за определенный период времени.

DAX предоставляет несколько функций Time Intelligence, которые помогают вам выполнять большинство таких вычислений. Однако для этих функций DAX требуется таблица Date для использования с другими таблицами в модели данных.

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

В этой главе вы поймете различные аспекты таблиц дат. Если вы знакомы с таблицами дат в модели данных Power Pivot, вы можете пропустить эту главу и перейти к следующим главам. В противном случае вы можете понять таблицы дат в модели данных Power Pivot.

Что такое таблица дат?

Таблица дат — это таблица в модели данных, имеющая как минимум один столбец непрерывных дат в течение требуемой продолжительности. Он может иметь дополнительные столбцы, представляющие разные периоды времени. Однако необходим столбец непрерывных дат, как того требуют функции DAX Time Intelligence.

Например,

• В таблице «Дата» могут быть столбцы, такие как «Дата», «Финансовый месяц», «Финансовый квартал» и «Финансовый год».

• Таблица Date может иметь столбцы, такие как Date, Month, Quarter и Year.

В таблице «Дата» могут быть столбцы, такие как «Дата», «Финансовый месяц», «Финансовый квартал» и «Финансовый год».

Таблица Date может иметь столбцы, такие как Date, Month, Quarter и Year.

Таблица дат с непрерывными датами

Предположим, вам необходимо произвести расчеты в диапазоне календарного года. Затем в таблице «Дата» должен быть хотя бы один столбец с непрерывным набором дат, включая все даты в этом конкретном календарном году.

Например, предположим, что данные, которые вы хотите просмотреть, имеют даты с 1 апреля 2014 года по 30 ноября 2016 года.

• Если вам необходимо составить отчет за календарный год, вам нужна таблица «Дата» с колонкой «Дата», которая содержит все даты с 1 января 2014 года по 31 декабря 2016 года в последовательности.

• Если вам нужно отчитаться о финансовом году, а конец финансового года — 30 июня, вам нужна таблица дат со столбцом — Дата, которая содержит все даты с 1 июля 2013 года по 30 июня 2017 года в последовательность.

• Если вам нужно отчитываться как по календарному, так и по финансовому году, то вы можете иметь одну таблицу дат, охватывающую требуемый диапазон дат.

Если вам необходимо составить отчет за календарный год, вам нужна таблица «Дата» с колонкой «Дата», которая содержит все даты с 1 января 2014 года по 31 декабря 2016 года в последовательности.

Если вам нужно отчитаться о финансовом году, а конец финансового года — 30 июня, вам нужна таблица дат со столбцом — Дата, которая содержит все даты с 1 июля 2013 года по 30 июня 2017 года в последовательность.

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

Ваша таблица дат должна содержать все дни для диапазона каждого года в данной продолжительности. Таким образом, вы получите смежные даты в течение этого периода времени.

Если вы регулярно обновляете свои данные новыми данными, конечная дата будет продлена на год или два, чтобы вам не приходилось часто обновлять таблицу дат.

Таблица дат выглядит следующим образом.

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

Вы можете добавить таблицу Date в модель данных любым из следующих способов:

• Импорт из реляционной базы данных или любого другого источника данных.

• Создание таблицы дат в Excel, а затем копирование или связывание с новой таблицей в Power Pivot.

• Импорт из Microsoft Azure Marketplace.

Импорт из реляционной базы данных или любого другого источника данных.

Создание таблицы дат в Excel, а затем копирование или связывание с новой таблицей в Power Pivot.

Импорт из Microsoft Azure Marketplace.

Создание таблицы дат в Excel и копирование в модель данных

Создание таблицы дат в Excel и копирование в модель данных — это самый простой и гибкий способ создания таблицы данных в модели данных.

• Откройте новый лист в Excel.

• Тип — Дата в первой строке столбца.

• Введите первую дату в диапазоне дат, который вы хотите создать, во второй строке в том же столбце.

• Выберите ячейку, щелкните маркер заполнения и перетащите его вниз, чтобы создать столбец непрерывных дат в необходимом диапазоне дат.

Откройте новый лист в Excel.

Тип — Дата в первой строке столбца.

Введите первую дату в диапазоне дат, который вы хотите создать, во второй строке в том же столбце.

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

Например, введите 01.01.2014, щелкните маркер заполнения и перетащите вниз, чтобы заполнить непрерывные даты до 31.12.2016.

• Нажмите столбец Дата.
• Нажмите вкладку INSERT на ленте.
• Нажмите Таблица.
• Проверьте диапазон таблицы.
• Нажмите ОК.

Таблица одного столбца дат готова в Excel.

• Выберите стол.
• Нажмите Копировать на ленте.
• Нажмите на окно Power Pivot.
• Нажмите Вставить на ленте.

Это добавит содержимое буфера обмена в новую таблицу в модели данных. Следовательно, вы можете использовать тот же метод для создания таблицы Date в существующей модели данных.

Откроется диалоговое окно «Вставить предварительный просмотр», как показано на следующем снимке экрана.

• Введите Дата в поле Имя таблицы.
• Предварительный просмотр данных.

• Установите флажок — Использовать первую строку в качестве заголовков столбцов.
• Нажмите ОК.

Это копирует содержимое буфера обмена в новую таблицу в модели данных.

Теперь у вас есть таблица Date в модели данных с одним столбцом непрерывных дат. Заголовок столбца — это дата, указанная в таблице Excel.

Добавление новых столбцов даты в таблицу дат

Затем вы можете добавить вычисляемые столбцы в таблицу Date согласно требованию для ваших расчетов.

Например, вы можете добавить столбцы — День, Месяц, Год и Квартал следующим образом —

• День

  = ДЕНЬ ( ‘Date’ [Дата])

• Месяц

  = МЕСЯЦ ( ‘Date’ [Дата])

• Год

  = YEAR ( ‘Date’ [Дата])

• четверть

  = CONCATENATE («QTR», INT ((‘Date’ [Month] +2) / 3))

= ДЕНЬ ( ‘Date’ [Дата])

= МЕСЯЦ ( ‘Date’ [Дата])

= YEAR ( ‘Date’ [Дата])

= CONCATENATE («QTR», INT ((‘Date’ [Month] +2) / 3))

Результирующая таблица Date в модели данных выглядит следующим образом.

Таким образом, вы можете добавить любое количество вычисляемых столбцов в таблицу Date. Что важно и необходимо, так это то, что в таблице «Дата» должен быть столбец непрерывных дат, охватывающий промежуток времени, в течение которого вы выполняете вычисления.

Создание таблицы дат для календарного года

Календарный год обычно включает в себя даты с 1 января по 31 декабря года, а также праздничные дни, отмеченные для этого конкретного года. При выполнении расчетов может потребоваться учитывать только рабочие дни, исключая выходные и праздничные дни.

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

• Создайте таблицу Excel со столбцом «Дата», состоящим из непрерывных дат с 1 января 2017 года по 31 декабря 2017 года. (Обратитесь к предыдущему разделу, чтобы узнать, как это сделать.)

• Скопируйте таблицу Excel и вставьте ее в новую таблицу в модели данных. (Обратитесь к предыдущему разделу, чтобы узнать, как это сделать.)

• Назовите таблицу как Календарь.

• Добавьте следующие рассчитанные столбцы —

  • День = ДЕНЬ («Календарь» [Дата])

  • Месяц = ​​МЕСЯЦ («Календарь» [Дата])

  • Год = ГОД («Календарь» [Дата])

  • День недели = ФОРМАТ («Календарь» [Дата], «DDD»)

  • Название месяца = ФОРМАТ («Календарь» [Дата], «МММ»)

Создайте таблицу Excel со столбцом «Дата», состоящим из непрерывных дат с 1 января 2017 года по 31 декабря 2017 года. (Обратитесь к предыдущему разделу, чтобы узнать, как это сделать.)

Скопируйте таблицу Excel и вставьте ее в новую таблицу в модели данных. (Обратитесь к предыдущему разделу, чтобы узнать, как это сделать.)

Назовите таблицу как Календарь.

Добавьте следующие рассчитанные столбцы —

День = ДЕНЬ («Календарь» [Дата])

Месяц = ​​МЕСЯЦ («Календарь» [Дата])

Год = ГОД («Календарь» [Дата])

День недели = ФОРМАТ («Календарь» [Дата], «DDD»)

Название месяца = ФОРМАТ («Календарь» [Дата], «МММ»)

Добавление праздников в таблицу календаря

Добавьте праздники в таблицу календаря следующим образом —

• Получить список объявленных праздников за год.

• Например, для США вы можете получить список праздников для любого требуемого года по следующей ссылке http://www.calendar-365.com/ .

• Скопируйте и вставьте их в лист Excel.

• Скопируйте таблицу Excel и вставьте ее в новую таблицу в модели данных.

• Назовите стол как Праздники.

Получить список объявленных праздников за год.

Например, для США вы можете получить список праздников для любого требуемого года по следующей ссылке http://www.calendar-365.com/ .

Скопируйте и вставьте их в лист Excel.

Скопируйте таблицу Excel и вставьте ее в новую таблицу в модели данных.

Назовите стол как Праздники.

• Затем вы можете добавить вычисляемый столбец выходных дней в таблицу Calendar, используя функцию DAX LOOKUPVALUE.

Затем вы можете добавить вычисляемый столбец выходных дней в таблицу Calendar, используя функцию DAX LOOKUPVALUE.

= LOOKUPVALUE (праздники [Праздник], Праздники [Дата], ‘Календарь’ [Дата])

Функция DAX LOOKUPVALUE ищет третий параметр, т.е. Calendar [Date], во втором параметре, т.е. Holidays [Date], и ​​возвращает первый параметр, т.е. Holidays [Holiday], если есть совпадение. Результат будет выглядеть так, как показано на следующем скриншоте.

Добавление столбцов в финансовый год

Финансовый год обычно включает даты с 1- ^го числа месяца после окончания финансового года до следующего финансового года. Например, если конец финансового года — 31 марта, финансовый год колеблется с 1 апреля по 31 марта.

Вы можете включить финансовые периоды времени в таблицу календаря, используя формулы DAX —

• Добавить меру для FYE

  FYE: = 3

• Добавьте следующие рассчитанные столбцы —

  • Отчетный год

    = ЕСЛИ ( ‘Календарь’ [Месяц] <= ‘Календарь’ [FYE], ‘календарь’ [год], ‘календарь’ [год] +1)

  • Финансовый месяц

    = IF («Календарь» [Месяц] <= «Календарь» [FYE], 12-«Календарь» [FYE] + «Календарь» [Месяц], «Календарь» [Месяц] — «Календарь» [FYE])

  • Фискальный квартал

    = INT ((‘Календарь’ [Финансовый месяц] +2) / 3)

Добавить меру для FYE

FYE: = 3

Добавьте следующие рассчитанные столбцы —

Отчетный год

= ЕСЛИ ( ‘Календарь’ [Месяц] <= ‘Календарь’ [FYE], ‘календарь’ [год], ‘календарь’ [год] +1)

Финансовый месяц

= IF («Календарь» [Месяц] <= «Календарь» [FYE], 12-«Календарь» [FYE] + «Календарь» [Месяц], «Календарь» [Месяц] — «Календарь» [FYE])

Фискальный квартал

= INT ((‘Календарь’ [Финансовый месяц] +2) / 3)

Установка свойства таблицы даты

При использовании функций DAX Time Intelligence, таких как TOTALYTD, PREVIOUSMONTH и DATESBETWEEN, для корректной работы требуются метаданные. Свойство таблицы дат устанавливает такие метаданные.

Чтобы установить свойство таблицы дат —

• Выберите таблицу календаря в окне Power Pivot.
• Перейдите на вкладку «Дизайн» на ленте.
• Нажмите Пометить как таблицу дат в группе Календари.
• Нажмите Mark as Date Table в раскрывающемся списке.

Откроется диалоговое окно «Пометить как таблицу дат». Выберите столбец «Дата» в таблице «Календарь». Это должен быть столбец типа данных Date и должен иметь уникальные значения. Нажмите ОК.

Расширение модели данных

В этой главе вы узнаете, как расширить модель данных, созданную в предыдущих главах. Расширение модели данных включает в себя —

• Добавление таблиц
• Добавление вычисляемых столбцов в существующую таблицу
• Создание мер в существующей таблице

Из них создание мер имеет решающее значение, так как оно предполагает предоставление новых данных о модели данных, которые позволят тем, кто использует модель данных, избежать переделок, а также сэкономить время при анализе данных и принятии решений.

Поскольку анализ прибылей и убытков предполагает работу с периодами времени, и вы будете использовать функции DAX Time Intelligence, вам потребуется таблица Date в модели данных.

Если вы новичок в таблицах дат, ознакомьтесь с главой — Общие сведения о таблицах дат.

Вы можете расширить модель данных следующим образом:

• Чтобы создать связь между таблицей данных, т. Е. Таблицей финансовых данных и таблицей дат, необходимо создать вычисляемый столбец Дата в таблице финансовых данных.

• Для выполнения различных типов вычислений необходимо создать взаимосвязи между таблицей данных — Финансовые данные и таблицами поиска — Счета и География.

• Вам необходимо создать различные меры, которые помогут вам выполнить несколько расчетов и провести необходимый анализ.

Чтобы создать связь между таблицей данных, т. Е. Таблицей финансовых данных и таблицей дат, необходимо создать вычисляемый столбец Дата в таблице финансовых данных.

Для выполнения различных типов вычислений необходимо создать взаимосвязи между таблицей данных — Финансовые данные и таблицами поиска — Счета и География.

Вам необходимо создать различные меры, которые помогут вам выполнить несколько расчетов и провести необходимый анализ.

Эти этапы по существу представляют собой этапы моделирования данных для анализа прибылей и убытков с использованием модели данных. Однако это последовательность шагов для любого типа анализа данных, который вы хотите выполнить с моделью данных Power Pivot.

Далее вы узнаете, как создавать показатели и как их использовать в сводных таблицах Power в последующих главах. Это даст вам достаточное понимание моделирования данных с помощью DAX и анализа данных с помощью Power PivotTables.

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

Создайте таблицу Date для периодов времени, охватывающих финансовые годы, следующим образом:

• Создайте таблицу с одним столбцом с заголовком — Дата и смежные даты в диапазоне от 1 июля 2011 года по 30 июня 2008 года на новом листе Excel.

• Скопируйте таблицу из Excel и вставьте ее в окно Power Pivot. Это создаст новую таблицу в модели данных Power Pivot.

• Назовите таблицу как Дата.

• Убедитесь, что столбец Date в таблице Date имеет тип данных — Date (DateTime).

Создайте таблицу с одним столбцом с заголовком — Дата и смежные даты в диапазоне от 1 июля 2011 года по 30 июня 2008 года на новом листе Excel.

Скопируйте таблицу из Excel и вставьте ее в окно Power Pivot. Это создаст новую таблицу в модели данных Power Pivot.

Назовите таблицу как Дата.

Убедитесь, что столбец Date в таблице Date имеет тип данных — Date (DateTime).

Далее необходимо добавить вычисляемые столбцы — финансовый год, финансовый квартал, финансовый месяц и месяц в таблицу «Дата» следующим образом:

Отчетный год

Предположим, что конец финансового года — 30 июня. Затем финансовый год длится с 1 июля по 30 июня. Например, период с 1 июля 2011 г. (01.07.2011) по 30 июня 2012 г. (30 июня 2012 г.) будет 2012 финансовым годом.

Предположим, вы хотите представить в таблице Date то же самое, что и FY2012.

• Вам необходимо сначала извлечь часть финансового года Даты и добавить ее в FY.

  • Для дат в период с июля 2011 года по декабрь 2011 года финансовый год составляет 1 + 2011.

  • Для дат в период с января 2012 года по июнь 2012 года финансовый год 0 + 2012.

  • Чтобы обобщить, если месяц финансового года — FYE, сделайте следующее:

    Целая часть ((месяц — 1) / год) + год

  • Далее, возьмите самые правильные 4 символа, чтобы получить финансовый год.

• В DAX вы можете представлять так же, как —

  ПРАВЫЙ (INT ((МЕСЯЦ ( ‘Дата’ [Дата]) — 1) / ‘Дата’ [FYE]) + Год ( ‘Дата’ [Дата]), 4)

• Добавьте вычисляемый столбец Fiscal Year в таблицу Date с формулой DAX —

  = «ФГ» & ПРАВЫЙ (INT ((МЕСЯЦ ( ‘Дата’ [Дата]) — 1) / ‘Дата’ [FYE]) + Год ( ‘Дата’ [Дата]), 4)

Вам необходимо сначала извлечь часть финансового года Даты и добавить ее в FY.

Для дат в период с июля 2011 года по декабрь 2011 года финансовый год составляет 1 + 2011.

Для дат в период с января 2012 года по июнь 2012 года финансовый год 0 + 2012.

Чтобы обобщить, если месяц финансового года — FYE, сделайте следующее:

Целая часть ((месяц — 1) / год) + год

Далее, возьмите самые правильные 4 символа, чтобы получить финансовый год.

В DAX вы можете представлять так же, как —

ПРАВЫЙ (INT ((МЕСЯЦ ( ‘Дата’ [Дата]) — 1) / ‘Дата’ [FYE]) + Год ( ‘Дата’ [Дата]), 4)

Добавьте вычисляемый столбец Fiscal Year в таблицу Date с формулой DAX —

= «ФГ» & ПРАВЫЙ (INT ((МЕСЯЦ ( ‘Дата’ [Дата]) — 1) / ‘Дата’ [FYE]) + Год ( ‘Дата’ [Дата]), 4)

Фискальный квартал

Если FYE представляет месяц финансового года, финансовый квартал получается как

Целая часть ((Остаток ((Месяц + FYE-1) / 12) + 3) / 3)

• В DAX вы можете представлять так же, как —

  INT ((MOD (МЕСЯЦ ( ‘Дата’ [Дата]) + ‘Дата’ [FYE] -1,12) + 3 ) / 3)

• Добавьте вычисляемый столбец Fiscal Quarter в таблицу Date с формулой DAX —

  = ‘Date’ [FiscalYear] & «- Q» & FORMAT (INT ((MOD (MONTH (‘Date’ [Date]) + ‘Date’ [FYE] -1,12) + 3) / 3), «0» )

В DAX вы можете представлять так же, как —

INT ((MOD (МЕСЯЦ ( ‘Дата’ [Дата]) + ‘Дата’ [FYE] -1,12) + 3 ) / 3)

Добавьте вычисляемый столбец Fiscal Quarter в таблицу Date с формулой DAX —

= ‘Date’ [FiscalYear] & «- Q» & FORMAT (INT ((MOD (MONTH (‘Date’ [Date]) + ‘Date’ [FYE] -1,12) + 3) / 3), «0» )

Финансовый месяц

Если FYE представляет конец финансового года, период финансового месяца получается как

(Остаток (Месяц + FYE-1) / 12) + 1

• В DAX вы можете представлять так же, как —

  MOD (МЕСЯЦ ( ‘Дата’ [Дата]) + ‘Дата’ [FYE] -1,12) +1

• Добавьте вычисляемый столбец Fiscal Month в таблицу Date с формулой DAX —

  = ‘Дата’ [Финансовый год] & «- P» & ФОРМАТ (MOD (МЕСЯЦ ([Дата]) + [FYE] -1,12) +1, «00»)

В DAX вы можете представлять так же, как —

MOD (МЕСЯЦ ( ‘Дата’ [Дата]) + ‘Дата’ [FYE] -1,12) +1

Добавьте вычисляемый столбец Fiscal Month в таблицу Date с формулой DAX —

= ‘Дата’ [Финансовый год] & «- P» & ФОРМАТ (MOD (МЕСЯЦ ([Дата]) + [FYE] -1,12) +1, «00»)

Месяц

Наконец, добавьте вычисляемый столбец Месяц, который представляет номер месяца в финансовом году, следующим образом:

= ФОРМАТ (MOD (МЕСЯЦ ([Дата]) + [FYE] -1,12) +1, «00») & «-» & ФОРМАТ ([Дата], «ммм»)

Результирующая таблица Date выглядит следующим образом.

Пометьте таблицу — Date as Date Table в столбце — Date как столбец с уникальными значениями, как показано на следующем снимке экрана.

Добавление вычисляемых столбцов

Чтобы создать связь между таблицей «Финансовые данные» и таблицей «Дата», требуется столбец значений «Дата» в таблице «Финансовые данные».

• Добавьте вычисляемый столбец Дата в таблице «Финансовые данные» с формулой DAX —

  = DATEVALUE («Финансовые данные» [Финансовый месяц])

Добавьте вычисляемый столбец Дата в таблице «Финансовые данные» с формулой DAX —

= DATEVALUE («Финансовые данные» [Финансовый месяц])

Определение отношений между таблицами в модели данных

У вас есть следующие таблицы в модели данных —

• Таблица данных — Финансовые данные
• Таблицы поиска — счета и география Locn
• Таблица дат — Дата

Чтобы определить отношения между таблицами в модели данных, выполните следующие действия:

• Просмотрите таблицы в представлении схемы Power Pivot.

• Создайте следующие отношения между таблицами —

  • Связь между таблицей данных финансов и таблицей счетов со столбцом Счет.

  • Связь между таблицей финансовых данных и географической таблицей Locn с колонкой Profit Center.

  • Связь между таблицей финансовых данных и таблицей даты со столбцом Дата.

Просмотрите таблицы в представлении схемы Power Pivot.

Создайте следующие отношения между таблицами —

Связь между таблицей данных финансов и таблицей счетов со столбцом Счет.

Связь между таблицей финансовых данных и географической таблицей Locn с колонкой Profit Center.

Связь между таблицей финансовых данных и таблицей даты со столбцом Дата.

Скрытие столбцов из клиентских инструментов

Если в таблице данных есть какие-либо столбцы, которые вы не будете использовать в качестве полей в любой сводной таблице, их можно скрыть в модели данных. Тогда они не будут видны в списке полей сводной таблицы.

В таблице «Финансовые данные» у вас есть 4 столбца — финансовый месяц, дата, счет и центр прибыли, которые вы не будете использовать в качестве полей в любой сводной таблице. Следовательно, вы можете скрыть их, чтобы они не отображались в списке полей сводной таблицы.

• Выберите столбцы — Финансовый месяц, Дата, Счет и Центр прибыли в таблице Финансовые данные.

• Щелкните правой кнопкой мыши и выберите «Скрыть от инструментов клиента» в раскрывающемся списке.

Выберите столбцы — Финансовый месяц, Дата, Счет и Центр прибыли в таблице Финансовые данные.

Щелкните правой кнопкой мыши и выберите «Скрыть от инструментов клиента» в раскрывающемся списке.

Создание показателей в таблицах

Вы полностью готовы к моделированию и анализу данных с помощью DAX, используя модель данных и сводные таблицы Power.

В следующих главах вы узнаете, как создавать показатели и как их использовать в сводных таблицах Power. Вы создадите все показатели в таблице данных, то есть в таблице данных финансов.

Вы создадите меры с использованием формул DAX в таблице данных — Финансовые данные, которую можно использовать в любом количестве сводных таблиц для анализа данных. Меры по сути являются метаданными. Создание показателей в таблице данных является частью моделирования данных, а их суммирование в сводных таблицах Power — частью анализа данных.

Базовые финансовые показатели и анализ

В модели данных можно создавать различные меры, которые будут использоваться в любом количестве сводных таблиц Power. Это формирует процесс моделирования и анализа данных с помощью модели данных с использованием DAX.

Как вы узнали ранее в предыдущих разделах, моделирование и анализ данных зависят от конкретного бизнеса и контекста. В этой главе вы познакомитесь с моделированием и анализом данных на основе образца базы данных о прибылях и убытках, чтобы понять, как создавать необходимые показатели и использовать их в различных сводных таблицах Power.

Вы можете применить один и тот же метод для моделирования и анализа данных для любого бизнеса и контекста

Создание мер на основе финансовых данных

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

Чтобы выполнить расчеты для анализа данных о прибылях и убытках, вы можете создать такие показатели, как сумма, год к году, год к дате, квартал к дате, отклонение, численность персонала, затраты на численность персонала и т. Д. Вы можете используйте эти показатели в сводных таблицах Power для анализа данных и представления результатов анализа.

В следующих разделах вы узнаете, как создавать базовые финансовые показатели и анализировать данные с помощью этих показателей. Меры называются базовыми мерами, поскольку они могут использоваться при создании других финансовых мер. Вы также узнаете, как создавать показатели за предыдущие периоды времени и использовать их в анализе.

Создание базовых финансовых мер

В анализе финансовых данных главную роль играют бюджет и прогноз.

бюджет

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

Прогноз

Финансовый прогноз — это оценка будущих финансовых результатов компании путем изучения исторических данных о доходах и расходах компании. Вы можете использовать финансовое прогнозирование для следующего —

• Определить, как распределить бюджет на будущий период.

• Для отслеживания ожидаемых результатов деятельности компании.

• Принимать своевременные решения для устранения недостатков по отношению к целям или максимизировать появляющуюся возможность.

Определить, как распределить бюджет на будущий период.

Для отслеживания ожидаемых результатов деятельности компании.

Принимать своевременные решения для устранения недостатков по отношению к целям или максимизировать появляющуюся возможность.

Actuals

Для выполнения расчетов по бюджетированию и прогнозированию вам требуются фактические доходы и расходы в любой момент времени.

Вы можете создать следующие 3 базовых финансовых показателя, которые можно использовать при создании других финансовых показателей в режиме данных.

• Сумма бюджета
• Фактическая сумма
• Сумма прогноза

Этими показателями являются суммы агрегации по столбцам — Бюджет, Факт и Прогноз в таблице Финансовые данные.

Создайте базовые финансовые показатели следующим образом:

Сумма бюджета

Сумма бюджета: = СУММА («Финансовые данные» [Бюджет])

Фактическая сумма

Фактическая сумма: = СУММА («Финансовые данные» [Фактические])

Сумма прогноза

Сумма прогноза: = SUM («Финансовые данные» [Прогноз])

Анализ данных с помощью базовых финансовых показателей

С помощью базовых финансовых показателей и таблицы «Дата» вы можете выполнить свой анализ следующим образом:

• Создать Power PivotTable.
• Добавьте поле «Финансовый год» из таблицы «Дата» в строки.
• Добавьте показатели Сумма бюджета, Фактическая сумма и Сумма прогноза (которые отображаются в виде полей в списке Поля сводной таблицы) в Значения.

Создание финансовых показателей за предыдущие периоды

С помощью трех базовых финансовых показателей и таблицы «Дата» вы можете создавать другие финансовые показатели.

Предположим, вы хотите сравнить фактическую сумму за квартал с фактической суммой за предыдущий квартал. Вы можете создать показатель — фактическая сумма за предыдущий квартал.

Фактическая сумма за предыдущий квартал: = РАСЧЕТ ([Фактическая сумма], DATEADD («Дата» [Дата], 1, КВАРТАЛ))

Аналогичным образом вы можете создать показатель — Фактическая сумма за предыдущий год.

Фактическая сумма за предыдущий год: = РАСЧЕТ ([Фактическая сумма], DATEADD («Дата» [Дата], 1, ГОД))

Анализ данных с финансовыми показателями за предыдущие периоды

С помощью базовых показателей, показателей за предыдущие периоды и таблицы дат вы можете выполнить свой анализ следующим образом:

• Создать Power PivotTable.
• Добавьте поле Fiscal Quarter из таблицы Date в строки.
• Добавьте значения фактической суммы и фактической суммы за предыдущий квартал к значениям.
• Создайте еще одну Power PivotTable.
• Добавьте поле «Финансовый год» из таблицы «Дата» в строки.
• Добавьте значения фактической суммы и фактической суммы за предыдущий год к значениям.

Финансовые показатели и анализ в годовом исчислении

Год за годом (в годовом исчислении) является показателем роста. Он получается путем вычитания фактической суммы предыдущего года из фактической суммы.

Если результат положительный, он отражает увеличение фактического, а если он отрицательный, то отражает уменьшение фактического, т. Е. Если мы рассчитываем год к году как —

год к году = (фактическая сумма — фактическая сумма за предыдущий год)

• Если фактическая сумма> фактическая сумма предыдущего года, год за годом будет положительным.
• Если фактическая сумма <фактическая сумма предыдущего года, год за годом будет отрицательным.

В финансовых данных счета, такие как счета расходов, будут иметь дебетовые (положительные) суммы, а счета доходов будут иметь кредитные (отрицательные) суммы. Следовательно, для счетов расходов приведенная выше формула работает нормально.

Однако для счетов доходов это должно быть наоборот, т.е.

• Если фактическая сумма> фактическая сумма предыдущего года, год за годом должен быть отрицательным.
• Если фактическая сумма <фактическая сумма предыдущего года, год за годом должен быть положительным.

Следовательно, для счетов доходов вы должны рассчитать год к году как —

год к году = — (фактическая сумма — фактическая сумма предыдущего года)

Создание меры по сравнению с прошлым годом

Вы можете создать показатель «Год за годом» с помощью следующей формулы DAX —

Г / г: = ЕСЛИ (СОДЕРЖИТ (Счета, счета [Класс], «Чистый доход»), — ([Фактическая сумма] — [Фактическая сумма за предыдущий год]), [Фактическая сумма] — [Фактическая сумма за предыдущий год])

В приведенной выше формуле DAX —

• Функция DAX CONTAINS возвращает TRUE, если строка имеет «Чистый доход» в столбце Class в таблице «Счета».

• Функция DAX IF затем возвращает — ([Фактическая сумма] — [Фактическая сумма за предыдущий год]).

• В противном случае функция DAX IF возвращает [Фактическая сумма] — [Фактическая сумма за предыдущий год].

Функция DAX CONTAINS возвращает TRUE, если строка имеет «Чистый доход» в столбце Class в таблице «Счета».

Функция DAX IF затем возвращает — ([Фактическая сумма] — [Фактическая сумма за предыдущий год]).

В противном случае функция DAX IF возвращает [Фактическая сумма] — [Фактическая сумма за предыдущий год].

Создание процентного показателя в годовом исчислении

Вы можете представить год к году в процентах с соотношением —

(Г / г) / (фактическая сумма за предыдущий год)

Вы можете создать показатель Процент от года к году с помощью следующей формулы DAX —

% Г / г: = IF ([Фактическая сумма за предыдущий год], [Г / г] / ABS ([Фактическая сумма за предыдущий год]), ПУСТО ()

Функция DAX IF используется в приведенной выше формуле, чтобы гарантировать отсутствие деления на ноль.

Анализ данных с показателями по сравнению с прошлым годом

Создайте сводную таблицу Power следующим образом:

• Добавьте поля Class и Sub Class из таблицы Accounts в строки.
• Добавьте показатели — фактическую сумму, фактическую сумму за предыдущий год, год к году и% г / г к значениям.
• Вставьте слайсер в поле «Финансовый год» из таблицы «Дата».
• Выберите FY2016 в слайсере.

Создание бюджета по сравнению с прошлым годом

Вы можете создать показатель «Бюджет на каждый год» следующим образом:

Бюджет г / г: = ЕСЛИ (СОДЕРЖИТ (Счета, счета [Класс], «Чистый доход»), — ([Бюджетная сумма] — [Фактическая сумма за предыдущий год]), [Бюджетная сумма] — [Фактическая сумма за предыдущий год])

Создание бюджетного показателя в годовом исчислении

Вы можете создать показатель «Процент бюджета по сравнению с прошлым годом» следующим образом:

Бюджет г / г%: = ЕСЛИ ([Фактическая сумма за предыдущий год], [Бюджет г / г] / ABS ([Фактическая сумма за предыдущий год]), ПУСТО ()

Анализ данных с бюджетными мерами по сравнению с прошлым годом

Создайте сводную таблицу Power следующим образом:

• Добавьте поля Class и Sub Class из таблицы Accounts в строки.
• Добавьте показатели — Сумма бюджета, Фактическая сумма за предыдущий год, Годовой бюджет и Годовой бюджет% к значениям.
• Вставьте слайсер в поле «Финансовый год» из таблицы «Дата».
• Выберите FY2016 в слайсере.

Создание прогноза по сравнению с прошлым годом

Вы можете создать меру Прогноз по сравнению с прошлым годом следующим образом:

Прогноз г / г: = ЕСЛИ (СОДЕРЖИТ (счета, счета [класс], «чистый доход»), — ([прогнозная сумма] — [фактическая сумма предыдущего года]), [прогнозная сумма] — [фактическая сумма предыдущего года])

Создание прогноза процентного показателя за год

Вы можете создать показатель Прогноз Процент по сравнению с прошлым годом следующим образом:

Прогноз г / г%: = IF ([Фактическая сумма за предыдущий год], [Прогноз г / г] / ABS ([Фактическая сумма за предыдущий год]), ПУСТО ()

Анализ данных с прогнозными показателями по сравнению с прошлым годом

Создайте сводную таблицу Power следующим образом:

• Добавьте поля Class и Sub Class из таблицы Accounts в строки.
• Добавьте показатели — Сумма прогноза, Фактическая сумма за предыдущий год, Прогноз г / г и Прогноз г / г% к значениям.
• Вставьте слайсер в поле Финансовый год из таблицы данных.
• Выберите FY2016 в слайсере.

Измерения и анализ отклонений

Можно создать показатели отклонения, такие как отклонение от бюджета, отклонение от прогноза и прогнозное отклонение от бюджета. Вы также можете анализировать финансовые данные на основе этих мер.

Создание отклонения от показателя бюджетной суммы

Создайте показатель отклонения от суммы бюджета (сумма ВТБ) следующим образом:

Сумма ВТБ: = [Бюджетная сумма] — [Фактическая сумма]

Создание отклонения от показателя бюджета

Создайте показатель отклонения от бюджетного процента (% ВТБ) следующим образом:

% ВТБ: = IF ([Сумма бюджета], [Сумма ВТБ] / ABS ([Сумма бюджета]), ПУСТО ()

Анализ данных с отклонением от бюджетных показателей

Создайте сводную таблицу Power следующим образом:

• Добавьте финансовый год из таблицы дат в строки.
• Добавьте значения Фактическая сумма, Сумма бюджета, Сумма ВТБ,% ВТБ из таблицы Финансовые данные в Значения.

Создание отклонения от показателя суммы прогноза

Создайте показатель отклонения от суммы прогноза (сумма VTF) следующим образом:

VTF Sum: = [Прогнозная Сумма] — [Фактическая Сумма]

Создание отклонения от прогнозируемого показателя в процентах

Создайте показатель отклонения от прогнозируемого процента (VTF%) следующим образом:

VTF%: = IF ([прогнозная сумма], [VTF сумма] / ABS ([прогнозная сумма]), BLANK ())

Анализ данных с отклонением от прогнозируемых показателей

Создайте сводную таблицу Power следующим образом:

• Добавьте финансовый год из таблицы дат в строки.
• Добавьте значения Фактическая сумма, Прогнозная сумма, VTF Сумма, VTF% из таблицы Финансовые данные в Значения.

Создание отклонения прогноза от показателя суммы бюджета

Создайте меру Прогнозирование отклонения от суммы бюджета (Прогноз суммы ВТБ) следующим образом —

Прогноз суммы ВТБ: = [Бюджетная сумма] — [Прогнозная сумма]

Создание отклонения прогноза от показателя бюджета

Создайте меру Прогнозирование отклонения от процентного соотношения с бюджетом (прогноз ВТБ в процентах) следующим образом:

Прогноз VTB%: = IF ([Сумма бюджета], [Прогноз суммы ВТБ] / ABS ([Сумма бюджета]), BLANK ())

Анализ данных с прогнозом отклонения от бюджетных показателей

Создайте сводную таблицу Power следующим образом:

• Добавьте финансовый год из таблицы дат в строки.
• Добавьте значения «Сумма бюджета», «Сумма прогноза», «Прогноз суммы ВТБ», «Прогноз ВТБ%» из таблицы «Финансовые данные» в значения.

Годовые показатели и анализ

Чтобы рассчитать результат, включающий начальное сальдо от начала периода, такого как финансовый год, до определенного периода времени, вы можете использовать функции DAX Time Intelligence. Это позволит вам анализировать данные на уровне месяца.

В этой главе вы узнаете, как создавать текущие показатели и как проводить анализ данных с их помощью.

Создание показателя фактической суммы за текущий год

Создайте показатель фактической суммы с начала года следующим образом:

YTD Фактическая сумма: = TOTALYTD ([Фактическая сумма], «Дата» [Дата], ВСЕ («Дата»), «6/30»)

Создание показателя суммы бюджета на текущий год

Создайте показатель суммы бюджета на текущий год следующим образом:

Бюджетная сумма с начала года: = TOTALYTD ([Бюджетная сумма], «Дата» [Дата], ВСЕ («Дата»), «6/30»)

Создание показателя суммы прогноза на текущий год

Создайте показатель Сумма прогноза на текущий год следующим образом:

Сумма прогноза с начала года: = TOTALYTD ([Сумма прогноза], «Дата» [Дата], ВСЕ («Дата»), «6/30»)

Создание показателя фактической суммы за предыдущий год

Создайте показатель фактической суммы за предыдущий год на дату следующим образом:

Фактическая сумма до начала года: = TOTALYTD ([Фактическая сумма за предыдущий год], «Дата» [Дата], ВСЕ («Дата»), «6/30»)

Анализ данных с использованием текущих показателей

Создайте сводную таблицу Power следующим образом:

• Добавить таблицу «Месяц с даты» в строки.

• Добавьте значения Фактическая сумма, Фактическая сумма с начала года, Сумма бюджета с начала года и Сумма прогноза с начала года из таблицы Финансовые данные в Значения.

• Вставьте слайсер в финансовый год из таблицы дат.

• Выберите FY2016 в слайсере.

Добавить таблицу «Месяц с даты» в строки.

Добавьте значения Фактическая сумма, Фактическая сумма с начала года, Сумма бюджета с начала года и Сумма прогноза с начала года из таблицы Финансовые данные в Значения.

Вставьте слайсер в финансовый год из таблицы дат.

Выберите FY2016 в слайсере.

Создайте сводную таблицу Power следующим образом:

• Добавить таблицу «Месяц с даты» в строки.

• Добавьте значения Фактическая сумма, Фактическая сумма с начала года, Фактическая сумма за предыдущий год и Фактическая сумма с начала года с начала года из таблицы «Финансовые данные» в Значения.

• Вставьте слайсер в финансовый год из таблицы дат.

• Выберите FY2016 в слайсере.

Добавить таблицу «Месяц с даты» в строки.

Добавьте значения Фактическая сумма, Фактическая сумма с начала года, Фактическая сумма за предыдущий год и Фактическая сумма с начала года с начала года из таблицы «Финансовые данные» в Значения.

Вставьте слайсер в финансовый год из таблицы дат.

Выберите FY2016 в слайсере.

Ежеквартальные измерения и анализ

Чтобы рассчитать результат, включающий начальное сальдо с начала периода, такого как финансовый квартал, до определенного периода времени, вы можете использовать функции DAX Time Intelligence. Это позволит вам анализировать данные на уровне месяца.

В этой главе вы узнаете, как создавать квартальные показатели и как проводить анализ данных с их использованием.

Создание квартальной суммы

Создайте показатель фактической суммы за квартал следующим образом:

QTD Фактическая сумма: = TOTALQTD ([Фактическая сумма], «Дата» [Дата], ВСЕ («Дата»))

Создание квартальной бюджетной меры

Создайте меру «Бюджетная квартальная сумма» следующим образом:

QTD Бюджетная сумма: = TOTALQTD ([Бюджетная сумма], «Дата» [Дата], ВСЕ («Дата»))

Создание показателя суммы квартального прогноза

Создайте меру «Бюджетная квартальная сумма» следующим образом:

QTD Бюджетная сумма: = TOTALQTD ([Бюджетная сумма], «Дата» [Дата], ВСЕ («Дата»))

Создание показателя суммы квартального прогноза

Создайте меру «Квартальная прогнозная сумма» следующим образом:

QTD Forecast Sum: = TOTALQTD ([Forecast Sum], ‘Date’ [Date], ALL (‘Date’))

Создание предыдущего фактического итогового показателя за квартал

Создайте показатель фактической суммы за предыдущую квартальную дату следующим образом:

Предыдущая фактическая сумма QTD: = TOTALQTD ([Фактическая сумма предыдущего квартала], «Дата» [Дата], ВСЕ («Дата»))

Анализ данных с помощью квартальных показателей

Создайте сводную таблицу Power следующим образом:

• Добавить финансовый месяц из таблицы дат в строки.

• Добавьте значения Фактическая сумма, Фактическая сумма QTD, Бюджетная сумма QTD и Прогнозная сумма QTD из таблицы Финансовые данные в Значения.

• Вставьте слайсер в финансовый квартал из таблицы «Дата».

• Выберите FY2016-Q2 в слайсере.

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

Добавьте значения Фактическая сумма, Фактическая сумма QTD, Бюджетная сумма QTD и Прогнозная сумма QTD из таблицы Финансовые данные в Значения.

Вставьте слайсер в финансовый квартал из таблицы «Дата».

Выберите FY2016-Q2 в слайсере.

Создайте сводную таблицу Power следующим образом:

• Добавить финансовый месяц из таблицы дат в строки.

• Добавьте значения Фактическая сумма, Фактическая сумма QTD, Фактическая сумма за предыдущий квартал и Фактическая сумма за предыдущий квартал из таблицы Финансовые данные в Значения.

• Вставьте слайсер в таблицу «Фискальный квартал от даты».

• Выберите FY2016-Q1 в слайсере.

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

Добавьте значения Фактическая сумма, Фактическая сумма QTD, Фактическая сумма за предыдущий квартал и Фактическая сумма за предыдущий квартал из таблицы Финансовые данные в Значения.

Вставьте слайсер в таблицу «Фискальный квартал от даты».

Выберите FY2016-Q1 в слайсере.

Бюджетные меры и анализ

Бюджетирование включает оценку денежных потоков компании за финансовый год. Финансовое положение компании, ее цели, ожидаемые доходы и расходы учитываются при составлении бюджета.

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

В любое время в течение финансового года вы можете рассчитать следующее:

Неизрасходованный баланс

Неизрасходованный остаток — это бюджет, остающийся после фактических расходов, т.е.

Неизрасходованный остаток = сумма бюджета с начала года — фактическая сумма с начала года

Достижение бюджета%

Достижение бюджета% — это процент бюджета, который вы потратили на сегодняшний день, т.е.

Достижение бюджета% = фактическая сумма с начала года / бюджетная сумма с начала года

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

Создание неизрасходованной меры баланса

Вы можете создать меру Неизрасходованного баланса следующим образом:

Неизрасходованный остаток: = РАСЧЕТ ([Сумма бюджета с начала года], ВСЕ («Финансовые данные» [Дата])) — [Фактическая сумма с начала года]

Создание показателя процента достижения бюджета

Вы можете создать показатель Процент достижения бюджета следующим образом:

% Достижения бюджета: = ЕСЛИ ([Сумма бюджета с начала года], [Фактическая сумма с начала года] / РАСЧЕТ ([Сумма бюджета с начала года], ВСЕ («Финансовые данные» [Дата])), ПУСТО ()

Анализ данных с бюджетными мерами

Создайте сводную таблицу Power следующим образом:

• Добавьте Месяц из таблицы Даты в Строки.

• Добавьте значения бюджетной суммы, бюджетной суммы с начала года, фактической суммы с начала года,% достижения бюджета и неизрасходованного сальдо из таблицы финансовых данных в значения.

• Вставьте слайсер в поле «Финансовый год».

• Выберите FY2016 в слайсере.

Добавьте Месяц из таблицы Даты в Строки.

Добавьте значения бюджетной суммы, бюджетной суммы с начала года, фактической суммы с начала года,% достижения бюджета и неизрасходованного сальдо из таблицы финансовых данных в значения.

Вставьте слайсер в поле «Финансовый год».

Выберите FY2016 в слайсере.

Прогнозные меры и анализ

Вы можете использовать показатели Forecast для анализа финансовых данных и помочь организации внести необходимые коррективы в свои цели и задачи на год, чтобы привести результаты деятельности компании в соответствие с меняющимися требованиями бизнеса.

Вам необходимо регулярно обновлять прогнозы, чтобы не отставать от изменений. Затем вы можете сравнить самый последний прогноз с бюджетом на оставшуюся часть финансового года, чтобы компания могла внести необходимые корректировки в соответствии с изменениями в бизнесе.

В любое время в течение финансового года вы можете рассчитать следующее:

Прогноз достижения%

Процент достижения прогноза — это процент от суммы прогноза, которую вы потратили на сегодняшний день, т.е.

Прогноз достижения% = фактическая сумма с начала года / прогнозная сумма с начала года

Прогноз неизрасходованного баланса

Прогноз неизрасходованного остатка — это прогнозная сумма, остающаяся после фактических расходов, т.е.

Прогноз неизрасходованного остатка = сумма прогноза с начала года — фактическая сумма с начала года

Корректировка бюджета

Бюджетная корректировка — это корректировка бюджетной суммы, которую организация должна сделать (увеличение или уменьшение) на основе прогноза.

Корректировка бюджета = прогноз неизрасходованного остатка — неизрасходованный остаток

Бюджет необходимо увеличить, если полученное значение будет положительным. В противном случае его можно настроить для других целей.

Создание показателя процента достижения прогноза

Вы можете создать показатель процента достижения прогноза следующим образом:

Процент достижения прогноза: = IF ([Сумма прогноза с начала года], [Фактическая сумма с начала года] / [Сумма прогноза с начала года], ПУСТО ()

Создание прогноза неизрасходованного сальдо

Вы можете создать меру Forecast Unexpended Balance следующим образом —

Прогноз неизрасходованного остатка: = [Прогнозная сумма с начала года] — [Фактическая сумма с начала года]

Создание показателя корректировки бюджета

Вы можете создать меру корректировки бюджета следующим образом —

Корректировка бюджета: = [Прогноз неизрасходованного остатка] — [Неизрасходованный остаток]

Анализ данных с помощью прогнозных показателей

Создайте сводную таблицу Power следующим образом:

• Добавить таблицу «Месяц с даты» в строки.

• Добавьте значения бюджетной суммы, бюджетной суммы с начала года, фактической суммы с начала года,% достижения бюджета и неизрасходованного сальдо из таблицы финансовых данных в значения.

• Вставьте слайсер на финансовый год.

• Выберите FY2016 в слайсере.

Добавить таблицу «Месяц с даты» в строки.

Добавьте значения бюджетной суммы, бюджетной суммы с начала года, фактической суммы с начала года,% достижения бюджета и неизрасходованного сальдо из таблицы финансовых данных в значения.

Вставьте слайсер на финансовый год.

Выберите FY2016 в слайсере.

Количество месяцев Меры

Вы можете создать показатели «Количество месяцев», которые можно использовать при создании показателей «Количество сотрудников» и «Стоимость на душу населения». Эти меры учитывают различные значения столбца финансового месяца, в котором столбец «Фактический столбец» / «Бюджет» / «Прогноз» имеет ненулевые значения в таблице «Финансовые данные». Это необходимо, поскольку таблица «Финансовые данные» содержит нулевые значения в столбце «Факт» и эти строки должны быть исключены при расчете численности персонала и стоимости на душу населения.

Создание счета фактических месяцев

Вы можете создать меру «Количество фактических месяцев» следующим образом:

CountOfActualMonths: = CALCULATE (DISTINCTCOUNT (‘FinanceData’ [Финансовый месяц]), «Финансовые данные» [Actual] <> 0)

Создание показателя количества бюджетных месяцев

Вы можете создать меру «Количество бюджетных месяцев» следующим образом:

CountOfBudgetMonths: = CALCULATE (DISTINCTCOUNT (‘FinanceData’ [Финансовый месяц]), ‘Финансовые данные’ [Бюджет] <> 0)

Создание подсчета прогнозируемых месяцев

Вы можете создать меру Count of Forecast Months следующим образом —

CountOfForecastMonths: = CALCULATE (DISTINCTCOUNT (‘FinanceData’ [Финансовый месяц]), ‘Финансовые данные’ [Прогноз] <> 0)

Конечные меры по численности персонала

Вы можете создать показатели конечной численности за определенный период времени. Конечная численность персонала — это сумма людей на последнюю дату в указанном периоде, для которой у нас есть непустая сумма людей.

Конечная численность персонала получается следующим образом:

• За месяц — сумма людей в конце определенного месяца.

• За квартал — сумма людей в конце последнего месяца конкретного квартала.

• Для года — сумма людей в конце последнего месяца определенного года.

За месяц — сумма людей в конце определенного месяца.

За квартал — сумма людей в конце последнего месяца конкретного квартала.

Для года — сумма людей в конце последнего месяца определенного года.

Создание фактического конечного показателя численности персонала

Вы можете создать фактическую конечную численность персонала следующим образом:

Фактический конечный счетчик голов: = CALCULATE (SUM («Финансовые данные» [Фактические люди]), LASTNONBLANK («Финансовые данные» [Дата], IF (CALCULATE (SUM («Финансовые данные» [Фактические люди])), ALL (Счета) ) = 0, ПУСТО (), РАСЧЕТ (СУММА («Финансовые данные» [Фактические люди]), ВСЕ (счета)))), ВСЕ (счета)

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

Создание показателя контингента бюджетных средств

Вы можете создать показатель бюджетного конечного количества следующим образом:

Счетчик конечного бюджета: = CALCULATE (SUM («Финансовые данные» [Бюджетные люди]), LASTNONBLANK («Финансовые данные» [Дата], IF (CALCULATE (SUM («Финансовые данные» [Бюджетные люди])), ALL (счета) ) = 0, BLANK (), CALCULATE (SUM («Финансовые данные» [Бюджетные люди]), ALL (счета)))), ALL (счета))

Создание прогноза конечного показателя численности персонала

Вы можете создать показатель Forecast Ending Headcount следующим образом:

Счетчик конечных результатов прогноза: = CALCULATE (SUM («Финансовые данные» [Люди прогноза]), LASTNONBLANK («Финансовые данные» [Дата], IF (CALCULATE (SUM («Финансовые данные» [Люди прогноза])), ALL (Счета) ) = 0, ПУСТО (), РАСЧЕТ (СУММА («Финансовые данные» [Прогнозные люди]), ВСЕ (счета)))), ВСЕ (счета)

Создание фактического конечного показателя численности персонала за предыдущий год

Вы можете создать показатель фактической контингента за предыдущий год следующим образом:

Фактическая численность конечного конца предыдущего года: = CALCULATE («Финансовые данные» [Фактический конечный счет конечного числа], DATEADD («Дата» [Дата], — 1, ГОД))

Анализ данных с помощью конечных показателей численности персонала

Создайте сводную таблицу Power следующим образом:

• Добавьте поля Финансовый год и Месяц из таблицы Даты в Строки.

• Добавьте значения Фактическое окончание, Конечный бюджет, Прогнозируемое окончание, Фактическое окончание предыдущего года из таблицы «Финансовые данные» к значениям.

• Вставьте слайсер в поле «Финансовый год».

• Выберите FY2016 в слайсере.

Добавьте поля Финансовый год и Месяц из таблицы Даты в Строки.

Добавьте значения Фактическое окончание, Конечный бюджет, Прогнозируемое окончание, Фактическое окончание предыдущего года из таблицы «Финансовые данные» к значениям.

Вставьте слайсер в поле «Финансовый год».

Выберите FY2016 в слайсере.

Средние показатели численности персонала

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

Среднемесячная численность персонала — это сумма месячной численности персонала, деленная на количество месяцев в выборе.

Вы можете создать эти меры, используя функцию DAX AVERAGEX.

Создание фактического среднего показателя численности персонала

Вы можете создать показатель фактической средней численности следующим образом:

Фактическая средняя численность персонала: = AVERAGEX (ЗНАЧЕНИЯ («Финансовые данные» [Финансовый месяц]), [Фактическая численность конечного персонала])

Создание показателя средней численности бюджета

Вы можете создать показатель фактической средней численности следующим образом:

Средняя численность бюджета: = AVERAGEX (ЗНАЧЕНИЯ («Финансовые данные» [Финансовый месяц]), [Количество конечных голов бюджета])

Создание прогноза среднего показателя численности персонала

Вы можете создать показатель Forecast Average Headcount следующим образом:

Средняя прогнозируемая численность персонала: = AVERAGEX (VALUES («Финансовые данные» [Финансовый месяц]), [Фактический конечный подсчет голов])

Создание фактического среднего показателя численности персонала за предыдущий год

Вы можете создать показатель фактической средней численности персонала за предыдущий год следующим образом:

Фактическая средняя численность персонала в предыдущем году: = РАСЧЕТ («Финансовые данные» [Фактическая средняя численность персонала], DATEADD («Дата» [Дата], -1, ГОД))

Анализ данных со средними показателями численности персонала

Создайте сводную таблицу Power следующим образом:

• Добавьте поля Финансовый год и Месяц из таблицы Даты в Строки.

• Добавьте значения Фактическая средняя численность персонала, Средняя численность бюджета, Средняя прогнозная численность, Фактическая средняя численность за предыдущий год из таблицы «Финансовые данные» к значениям.

• Вставьте слайсер в поле «Финансовый год».

• Выберите FY2016 в слайсере.

Добавьте поля Финансовый год и Месяц из таблицы Даты в Строки.

Добавьте значения Фактическая средняя численность персонала, Средняя численность бюджета, Средняя прогнозная численность, Фактическая средняя численность за предыдущий год из таблицы «Финансовые данные» к значениям.

Вставьте слайсер в поле «Финансовый год».

Выберите FY2016 в слайсере.

Общее количество сотрудников

В предыдущих главах вы узнали, как создавать показатели «Количество месяцев» и «Средняя численность персонала». Вы можете использовать эти показатели для расчета базовых показателей численности персонала —

• Фактическая общая численность
• Общая численность бюджета
• Прогноз Общая численность персонала

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

Создание фактического показателя общей численности персонала

Вы можете создать фактическую общую численность персонала следующим образом:

Фактическая общая численность персонала: = «Финансовые данные» [Фактическая средняя численность персонала] * «Финансовые данные» [CountOfActualMonths]

Создание показателя общей численности бюджета

Вы можете создать показатель общей численности бюджета следующим образом:

Общая численность бюджета: = «Финансовые данные» [Средняя численность бюджета] * «Финансовые данные» [CountOfBudgetMonths]

Создание прогноза общего показателя численности персонала

Вы можете создать прогноз общего количества сотрудников следующим образом:

Общая численность прогноза: = «Финансовые данные» [Средняя численность прогноза] * «Финансовые данные» [CountOfForecastMonths]

Измерения и анализ численности персонала в годовом исчислении

В предыдущей главе вы узнали, как создавать базовые показатели численности персонала — то есть фактическую общую численность персонала, общую численность бюджета и прогнозируемую общую численность персонала.

В этой главе вы узнаете, как создавать показатели численности персонала в течение года и как вы можете анализировать данные с помощью этих показателей.

Создание фактического показателя численности персонала в годовом исчислении

Вы можете создать фактическую конечную численность по итогам года следующим образом:

Фактическая численность персонала на конец года: = [Фактическая численность персонала на конец] — [Фактическая численность персонала на конец года]

Создание фактического среднего показателя численности персонала за год

Вы можете создать фактическую среднюю численность персонала в годовом исчислении следующим образом:

Фактическая средняя численность персонала в годовом исчислении: = [Фактическая средняя численность персонала] — [Фактическая средняя численность персонала в предыдущем году]

Создание фактической общей численности персонала в годовом исчислении

Вы можете создать показатель фактической общей численности персонала в годовом исчислении следующим образом:

Фактическая общая численность персонала в годовом исчислении: = [Фактическая общая численность персонала] — [Фактическая общая численность персонала в предыдущем году]

Анализ данных с использованием фактических показателей численности персонала за год

Создайте сводную таблицу Power следующим образом:

• Добавьте поля «Фискальный квартал» и «Месяц» из таблицы «Дата» в строки.

• Добавьте значения — Фактическое число конечных голов в предыдущем году, Фактическое число конечных голов в предыдущем году, Фактическое число конечных голов в годовом исчислении к значениям.

• Вставьте слайсер в поле Финансовый год.

• Выберите FY2016 в слайсере.

Добавьте поля «Фискальный квартал» и «Месяц» из таблицы «Дата» в строки.

Добавьте значения — Фактическое число конечных голов в предыдущем году, Фактическое число конечных голов в предыдущем году, Фактическое число конечных голов в годовом исчислении к значениям.

Вставьте слайсер в поле Финансовый год.

Выберите FY2016 в слайсере.

Создайте еще одну Power PivotTable на том же листе следующим образом:

• Добавьте поля «Фискальный квартал» и «Месяц» из таблицы «Дата» в строки.

• Добавьте значения — Фактическая средняя численность персонала, Фактическая средняя численность населения за предыдущий год, Фактическая средняя численность населения в годовом исчислении к значениям.

Добавьте поля «Фискальный квартал» и «Месяц» из таблицы «Дата» в строки.

Добавьте значения — Фактическая средняя численность персонала, Фактическая средняя численность населения за предыдущий год, Фактическая средняя численность населения в годовом исчислении к значениям.

Подключите слайсер к этой сводной таблице следующим образом:

• Нажмите на слайсер.
• Перейдите на вкладку «Параметры» в разделе «Инструменты слайсера» на ленте.
• Нажмите Сообщить о подключениях.

Откроется диалоговое окно Report Connections.

• Выберите две вышеуказанные сводные таблицы.
• Нажмите ОК.

Создание показателя численности персонала по сравнению с прошлым годом

Вы можете создать показатель годовой численности на конец года в бюджете следующим образом:

Количество сотрудников в бюджете на конец года: = [Количество сотрудников на конец бюджета] — [Фактическое количество сотрудников на конец года]

Создание показателя средней численности персонала в годовом бюджете

Вы можете создать показатель средней численности персонала в годовом бюджете следующим образом:

Средняя численность бюджета в годовом исчислении: = [Средняя численность бюджета] — [Фактическая средняя численность персонала за предыдущий год]

Создание показателя общей численности персонала в годовом бюджете

Вы можете создать показатель общей численности персонала в годовом бюджете следующим образом:

Общая численность бюджета в годовом исчислении: = [Общая численность бюджета] — [Фактическая общая численность персонала за предыдущий год]

Создание годового прогноза Конечная мера численности персонала

Вы можете создать показатель численности конечного прогноза на конец года следующим образом:

Прогнозируемое количество конечных сотрудников в годовом исчислении: = [Прогнозируемое количество конечных сотрудников] — [Фактическое количество конечных сотрудников в предыдущем году]

Создание среднегодового прогноза показателя численности персонала

Вы можете создать среднегодовой показатель прогноза численности персонала следующим образом:

Среднегодовая прогнозируемая численность персонала: = [Прогнозная численность персонала] — [Фактическая средняя численность персонала в предыдущем году]

Создание показателя общей численности персонала в годовом прогнозе

Вы можете создать показатель общей численности персонала в годовом прогнозе следующим образом:

Прогнозная общая численность персонала в годовом исчислении: = [Общая прогнозная численность персонала] — [Фактическая общая численность персонала в предыдущем году]

Дисперсионные меры численности

Вы можете создать показатели Variance Headcount на основе показателей Headcount, которые вы создали до сих пор.

Создание отклонения от бюджетного показателя

Вы можете создать дисперсию для показателя численности персонала, заканчивающегося по бюджету, следующим образом:

Счетчик конечных показателей ВТБ: = «Финансовые данные» [Счетчик конечных показателей бюджета] — «Финансовые данные» [Фактический показатель конечных показателей]

Создание отклонения от показателя средней численности бюджета

Вы можете создать показатель отклонения от средней средней численности бюджета следующим образом:

Средняя численность персонала ВТБ: = «Финансовые данные» [Средняя численность бюджета] — «Финансовые данные» [Фактическая средняя численность персонала

Создание отклонения от общего показателя численности бюджета

Вы можете создать показатель отклонения от общей суммы бюджета следующим образом:

Общая численность персонала ВТБ: = «Финансовые данные» [Общая численность бюджета] — «Финансовые данные» [Фактическая общая численность персонала]

Создание отклонения от прогноза Конечная мера численности персонала

Вы можете создать дисперсию для прогноза конечной численности персонала следующим образом:

Счетчик конечных чисел VTF: = «Финансовые данные» [Прогнозируемый конечный счетчик] — «Финансовые данные» [Фактический конечный счетчик]

Создание отклонения от прогноза среднего показателя численности персонала

Вы можете создать показатель отклонения от среднего прогноза численности персонала следующим образом:

Средняя численность персонала VTF: = «Финансовые данные» [Средняя численность прогноза] — «Финансовые данные» [Фактическая средняя численность персонала]

Создание отклонения для прогноза общей численности персонала

Вы можете создать дисперсию для прогноза общей численности персонала следующим образом:

Общая численность персонала VTF: = «Финансовые данные» [Общая численность прогноза] — «Финансовые данные» [Фактическая общая численность персонала]

Создание отклонения прогноза от показателя численности персонала, заканчивающегося на бюджет

Вы можете создать прогнозную дисперсию для показателя численности персонала, заканчивающегося по бюджету, следующим образом:

Прогноз количества конечных голов ВТБ: = «Финансовые данные» [Счет конечных показателей бюджета] — «Финансовые данные» [Прогноз конечных показателей]

Создание отклонения прогноза от показателя средней численности бюджета

Вы можете создать прогнозирование отклонения от среднего показателя численности бюджета следующим образом:

Прогноз средней численности персонала ВТБ: = «Финансовые данные» [Средняя численность бюджета] — «Финансовые данные» [Средняя численность прогноза]

Создание отклонения прогноза от общего показателя численности бюджета

Вы можете создать прогнозирование отклонения до показателя общей численности бюджета следующим образом:

Прогноз общего количества сотрудников ВТБ: = «Финансовые данные» [Общая численность бюджета] — «Финансовые данные» [Общий прогноз численности персонала

Стоимость и анализ численности персонала

Вы узнали о двух основных категориях мер —

• Финансовые меры.
• Меры по численности персонала.

Третья важная категория мер, которую вы изучите, — это меры по оценке стоимости людей. Любая организация будет заинтересована узнать годовую стоимость на душу населения. Годовая стоимость на душу населения представляет затраты компании на одного сотрудника в течение всего года.

Чтобы создать показатели затрат на душу населения, сначала необходимо создать определенные предварительные показатели затрат на людей. В таблице «Счета» имеется столбец «Подкласс», в котором в качестве одного из значений указаны «Люди». Следовательно, вы можете применить фильтр к таблице «Счета» в столбце «Подкласс», чтобы получить контекст фильтра для таблицы «Финансовые данные», чтобы получить стоимость сотрудников.

Таким образом, вы можете использовать показатели затрат на людей и показатели количества месяцев для создания показателей стоимости людей в годовом исчислении. Наконец, вы можете создать показатели «Годовая стоимость на душу населения» из показателей «Годовая стоимость персонала» и «Средняя численность персонала».

Создание фактической оценки стоимости людей

Вы можете создать меру фактической стоимости людей следующим образом:

Фактическая стоимость сотрудников: = РАСЧЕТ («Финансовые данные» [Фактическая сумма], ФИЛЬТР («Финансовые данные», СВЯЗАННЫЕ (счета [подкласс]) = «Люди»))

Создание бюджетного показателя стоимости людей

Вы можете создать показатель Cost People бюджета следующим образом:

Бюджет Люди Стоимость: = РАСЧЕТ («Финансовые данные» [Сумма бюджета], ФИЛЬТР («Финансовые данные», СВЯЗАННЫЕ (Счета [Подкласс]) = «Люди»))

Создание прогноза стоимости людей

Вы можете создать показатель Прогнозная стоимость людей следующим образом:

Прогноз стоимости людей: = РАСЧЕТ (‘Финансовые данные’ [Сумма прогноза], ФИЛЬТР (‘Финансовые данные’, СВЯЗАННЫЕ (Счета [Подкласс]) = «Люди»))

Создание годовой фактической оценки стоимости людей

Вы можете создать годовой показатель фактической стоимости людей следующим образом:

Годовая фактическая стоимость людей: = ЕСЛИ ([CountOfActualMonths], [Фактическая стоимость людей] * 12 / [CountOfActualMonths], ПУСТО ()

Создание показателя стоимости людей в годовом бюджете

Вы можете создать меру стоимости годового бюджета на людей следующим образом:

Годовые бюджетные расходы на людей: = IF ([CountOfBudgetMonths], [Бюджетные расходы на людей] * 12 / [CountOfBudgetMonths], BLANK ())

Создание годовой прогноз стоимости людей

Вы можете создать показатель Годовой прогноз стоимости людей следующим образом:

Годовая стоимость прогноза для людей: = IF ([CountOfForecastMonths], [Прогнозная стоимость людей] * 12 / [CountOfForecastMonths], BLANK ())

Создание фактической годовой стоимости на душу населения

Вы можете создать фактическую годовую стоимость на душу населения следующим образом:

Фактическая годовая цена за тысячу показов: = IF ([Фактическая средняя численность персонала], [Годовая фактическая стоимость персонала] / [Фактическая средняя численность персонала], ПУСТО ())

Создание бюджета Годовая стоимость на душу населения

Вы можете создать показатель годовой стоимости бюджета на душу населения (CPH) следующим образом:

Годовой бюджет CPH: = IF ([Средняя численность бюджета], [Годовая стоимость бюджета на людей] / [Средняя численность бюджета], BLANK ())

Создание прогнозной годовой стоимости на душу населения

Вы можете создать показатель Прогнозная годовая стоимость на душу населения (CPH) следующим образом:

Прогноз в годовом исчислении CPH: = IF ([Среднегодовой прогноз численности], [Годовой прогноз численности персонала] / [Среднегодовой прогноз численности], ПУСТО ()

Создание фактической годовой стоимости на душу населения за предыдущий год

Вы можете создать показатель фактической годовой стоимости на душу населения (CPH) за предыдущий год следующим образом:

Фактический годовой CPH за предыдущий год: = РАСЧЕТ ([Фактический годовой CPH в год], DATEADD («Дата» [Дата], — 1, ГОД))

Анализ данных с показателями затрат на душу населения

Создайте сводную таблицу Power следующим образом:

• Добавьте поля Строка Фискальный квартал и Финансовый месяц с даты в строки.

• Добавьте в столбцы показатели Фактический годовой CPH, Годовой бюджет CPH и Прогнозный годовой CPH.

• Добавьте поле «Финансовый год с даты» в «Фильтры».

• Выберите FY2016 в Фильтре.

Добавьте поля Строка Фискальный квартал и Финансовый месяц с даты в строки.

Добавьте в столбцы показатели Фактический годовой CPH, Годовой бюджет CPH и Прогнозный годовой CPH.

Добавьте поле «Финансовый год с даты» в «Фильтры».

Выберите FY2016 в Фильтре.

Создайте еще одну сводную таблицу Power следующим образом:

• Добавьте поле Фискальный квартал из таблицы Даты в Строки.

• Добавьте в столбцы показатели Фактическая годовая цена за год и Фактическая годовая цена за год.

• Вставьте слайсер в поле Fiscal Year from Date table.

• Выберите FY2015 и FY2016 на слайсере.

Добавьте поле Фискальный квартал из таблицы Даты в Строки.

Добавьте в столбцы показатели Фактическая годовая цена за год и Фактическая годовая цена за год.

Вставьте слайсер в поле Fiscal Year from Date table.

Выберите FY2015 и FY2016 на слайсере.

Оценить дисперсию и дисперсию объема

Вы узнали, как создавать показатели для годовой стоимости на душу населения и общей численности персонала. Вы можете использовать эти показатели для создания показателей «Дисперсия скорости» и «Дисперсия объема».

• Показатели отклонения ставки рассчитывают, какая часть отклонения валюты вызвана различиями в стоимости на душу населения.

• Показатели «Дисперсия объема» рассчитывают, какая часть отклонения валюты определяется колебаниями численности персонала.

Показатели отклонения ставки рассчитывают, какая часть отклонения валюты вызвана различиями в стоимости на душу населения.

Показатели «Дисперсия объема» рассчитывают, какая часть отклонения валюты определяется колебаниями численности персонала.

Создание отклонения от показателя бюджетной ставки

Вы можете создать показатель отклонения от бюджетной ставки следующим образом:

Коэффициент ВТБ: = ([Годовой годовой CPH] / 12- [Фактический годовой CPH] / 12) * [Фактический общий подсчет сотрудников]

Создание отклонения от показателя объема бюджета

Вы можете создать показатель отклонения от объема бюджета следующим образом:

Объем ВТБ: = [Общая численность ВТБ] * [Годовой бюджет CPH] / 12

Анализ данных с отклонением от бюджетных показателей

Создайте сводную таблицу Power следующим образом:

• Добавьте поля Строка Фискальный квартал и Финансовый месяц с даты в строки.
• Добавьте к значениям показатели Фактический годовой CPH, Бюджетный годовой CPH, Курс ВТБ, Объем ВТБ, Сумма ВТБ.
• Добавьте поля «Финансовый год из таблицы дат» и «Подкласс из таблицы счетов» в «Фильтры».
• Выберите 2016 финансовый год в фильтре финансового года.
• Выберите «Люди» в фильтре подклассов.
• Метки строк фильтра для значений финансового квартала FY2016-Q1 и FY2016-Q2.

В приведенной выше сводной таблице вы можете наблюдать следующее:

• Показанная сумма ВТБ указана только для Подкласса — Люди.

• В финансовом квартале-1 квартале 2016 года сумма ВТБ составляет 4 705 568 долларов США, ставка ВТБ — 970 506 297 долларов США, а объем ВТБ — 965 800 727 долларов США.

• Показатель ВТБ рассчитывает, что 970 506 297 долл. США Разница в бюджете (сумма ВТБ) вызвано разницей в затратах на душу населения, а -965 800 727 долл. США — разницей в численности персонала.

• Если вы добавите ставку ВТБ и объем ВТБ, вы получите 4 705 568 долл. США, то же самое значение, которое возвращается суммой ВТБ для людей из подкласса.

• Аналогичным образом, для финансового квартала 2016–2016 гг. Ставка ВТБ составляет 1 281 467 662 долл. США, а объем ВТБ — 1 210 710 978 долл. США. Если вы добавите ставку ВТБ и объем ВТБ, вы получите $ 70 756 678, то есть значение суммы ВТБ, указанное в сводной таблице.

Показанная сумма ВТБ указана только для Подкласса — Люди.

В финансовом квартале-1 квартале 2016 года сумма ВТБ составляет 4 705 568 долларов США, ставка ВТБ — 970 506 297 долларов США, а объем ВТБ — 965 800 727 долларов США.

Показатель ВТБ рассчитывает, что 970 506 297 долл. США Разница в бюджете (сумма ВТБ) вызвано разницей в затратах на душу населения, а -965 800 727 долл. США — разницей в численности персонала.

Если вы добавите ставку ВТБ и объем ВТБ, вы получите 4 705 568 долл. США, то же самое значение, которое возвращается суммой ВТБ для людей из подкласса.

Аналогичным образом, для финансового квартала 2016–2016 гг. Ставка ВТБ составляет 1 281 467 662 долл. США, а объем ВТБ — 1 210 710 978 долл. США. Если вы добавите ставку ВТБ и объем ВТБ, вы получите $ 70 756 678, то есть значение суммы ВТБ, указанное в сводной таблице.

Создание показателя годовой ставки

Вы можете создать показатель годовой ставки следующим образом:

Показатель г / г: = ([Фактическая годовая цена за год] / 12- [Фактическая годовая цена за год за предыдущий год] / 12) * [Фактическая общая численность персонала]

Создание показателя объема в годовом исчислении

Вы можете создать показатель годового объема следующим образом:

Объем в годовом исчислении: = [Фактическая общая численность в годовом исчислении] * [Фактический годовой CPH за предыдущий год] / 12

Создание отклонения от показателя прогноза

Вы можете создать показатель отклонения от прогнозируемой скорости следующим образом:

Коэффициент VTF: = ([Прогноз Годовой CPH в год] / 12- [Фактический годовой CPH] / 12) * [Фактический общий счетчик голов]

Создание дисперсии для измерения объема прогноза

Вы можете создать показатель отклонения от объема прогноза следующим образом:

Объем VTF: = [Общее количество голов VTF] * [Прогноз в годовом исчислении CPH] / 12

Анализ данных с отклонением от прогнозируемых показателей

Создайте сводную таблицу Power следующим образом:

• Добавьте поля Строка Фискальный квартал и Финансовый месяц с даты в строки.

• Добавьте к значениям показатели Фактический годовой CPH, Прогнозный годовой CPH, Коэффициент VTF, Объем VTF, Сумма VTF.

• Добавьте поля «Финансовый год из таблицы дат» и «Подкласс из таблицы счетов» в «Фильтры».

• Выберите 2016 финансовый год в фильтре финансового года.

• Выберите «Люди» в фильтре подклассов.

• Метки строк фильтра для значений финансового квартала FY2016-Q1 и FY2016-Q2.

Добавьте поля Строка Фискальный квартал и Финансовый месяц с даты в строки.

Добавьте к значениям показатели Фактический годовой CPH, Прогнозный годовой CPH, Коэффициент VTF, Объем VTF, Сумма VTF.

Добавьте поля «Финансовый год из таблицы дат» и «Подкласс из таблицы счетов» в «Фильтры».

Выберите 2016 финансовый год в фильтре финансового года.

Выберите «Люди» в фильтре подклассов.

Метки строк фильтра для значений финансового квартала FY2016-Q1 и FY2016-Q2.

Создание отклонения прогноза от показателя бюджетной ставки

Вы можете создать меру Прогнозирование отклонения от бюджетной ставки следующим образом —

Прогноз ставки ВТБ: = ([Годовая годовая цена за тысячу показов] / 12- [Прогноз годовой цены за тысячу показов] / 12) * [Прогноз общей численности персонала]

Создание отклонения прогноза от показателя объема бюджета

Вы можете создать показатель Прогнозирование отклонения от объема бюджета следующим образом:

Прогноз объема ВТБ: = [Прогноз общего количества голов ВТБ] * [Бюджет в годовом исчислении CPH] / 12

Анализ данных с прогнозом отклонения от бюджетных показателей

Создайте сводную таблицу Power следующим образом:

Добавьте поля Строка Фискальный квартал и Финансовый месяц с даты в строки.

Добавьте значения «Годовой CPH в годовом исчислении», «Прогнозный годовой CPH», «Прогноз ставки ВТБ», «Прогноз объема ВТБ», «Прогноз суммы ВТБ» к значениям.

Добавьте поля «Финансовый год из таблицы дат» и «Подкласс из таблицы счетов» в «Фильтры».

Выберите 2016 финансовый год в фильтре финансового года.

Выберите «Люди» в фильтре подклассов.

Метки строк фильтра для значений финансового квартала FY2016-Q1 и FY2016-Q2.