Как в Excel 2010 подсчитать различные значения с помощью DAX

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

Итак, DAX позволяет подсчитать количество различных значений, соответствующих условиям фильтра. Осознали ли вы всю притягательную силу этого утверждения? Опытные пользователи, создавшие не один десяток сложных сводных таблиц, нередко высказывали претензии по поводу их ограниченных вычислительных возможностей. В разделе «Использование макросов для улучшения отчетов сводных таблиц», посвященном VBA, предлагается довольно сложная формула =1/СЧЕТЕСЛИМН (…) для подсчета отличающихся значений. Средствами DAX можно подсчитать количество различных значений, которые соответствуют условиям фильтра.

Для создания новой меры с помощью DAX щелкните на кнопке Создать меру (New Measure), находящейся на вкладке PowerPivot. Обратите внимание: в данном случае речь идет не о вкладке в окне PowerPivot, а о вкладке PowerPivot, находящейся на ленте Excel. Итак, щелкните на значке Создать меру (рис. 10.30).

Рис. 10.30. Кнопка Создать меру находится на вкладке PowerPivot ленты Excel 2010

Рис. 10.30. Кнопка Создать меру находится на вкладке PowerPivot ленты Excel 2010

После щелчка на кнопке Создать меру на экране появляется диалоговое окно Параметры меры (Measure Settings), показанное на рис. 10.31.

Рис. 10.31. Измените параметры меры а этом диалоговом окне

Рис. 10.31. Измените параметры меры а этом диалоговом окне

  1. В поле Имя таблицы (Table Name) указывается имя базовой таблицы, в которой находятся основные числовые данные. С помощью раскрывающегося списка измените значение в этом поле с Weather на Sales.
  2. В поле Имя меры (Measure Name) введите имя DayCount.
  3. В поле Пользовательское имя используется то же имя, что и в поле Имя меры.
  4. качестве мер всегда выбираются агрегирующие функции, а не функции уровня ячейки. Поэтому остановитесь на агрегирующей функции, такой как sum либо COUNTROWS.
  5. В рассматриваемом примере применяется «магическая» функция Distinct (Sales [Date] ). Эта функция для каждой ячейки сводной таблицы возвращает перечень различных значений для строк, которые соответствуют условиям фильтра.
  6. После ввода формулы щелкните на кнопке Проверить формулу (Check Formula), чтобы убедиться в корректности синтаксиса формулы (рис. 10.32).

Обратите внимание на то, что функция Distinct должна использоваться в столбце базовой таблицы. Ее невозможно применять к значениям, находящимся в связанной таблице. Конечно, результат ее применения может изменяться с помощью фильтров, созданных в связанной таблице, но все же значения, по отношению к которым применяется функция Distinct, должны находиться в базовой таблице. В результате применения этой функции возвращается состоящая из одного столбца таблица, содержащая перечень различных значений. Для подсчета количества значений воспользуйтесь формулой =CountRows(Distinct(Sales[Date])).

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

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

В рассматриваемом случае под именем DayCount подразумевается промекуточный результат, который иллюстрирует концепцию использования функции Distinct. Затем можно определить меру путем деления Net Sales на DayCoimt.
В этом случае следует пропустить набор значений DayCount и создать меру Sales Per Day с помощью единственной формулы =SUM(Sales[Net Sales])/COUNTROWS(Distinct(Sales[Date])). На рис. 10.33 показаны значения в столбце продаж за день (Sales Per Day), зависящие от интенсивности осадков и местоположения торговой точки.

Рис. 10.33. Продажи в обеих торговых точках увеличиваются в солнечные дни

Рис. 10.33. Продажи в обеих торговых точках увеличиваются в солнечные дни

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

Обратите внимание на некорректно вычисленный итог (рис. 10.33). В строке Общий итог показатель продажи за день для торговой точки, находящейся в аэропорту, вычислен правильно (примерно 2665 долл.). Но если общий итог по продажам за день для аэропорта равен 2665 долл., а для торгового центра — 4526 долл., то почему в столбце Общий итог отображается величина 5833 долл.?

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

Рис. 10.34. Обратите внимание на то, что в выделенной ячейке может быть ошибка

Рис. 10.34. Обратите внимание на то, что в выделенной ячейке может быть ошибка

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

В столбце Итог Продажи показан общий итог по продажам в обеих торговых точках. Вычисляемый столбец Дни предназначен для подсчета количества дней, в течение которых открыта хотя бы одна торговая точка. Общий объем продаж для обеих торговых точек составил 6,3 млн долл. Но поскольку обе торговые точки не всегда работали в одно время, вычисление среднего объема продаж за день путем деления 6,3 млн на 1086 дней некорректно.

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

LocationDayCount =CountRows(Distinct(Sales[LocationDays]))Sales Per Store Per Day =Sum (Sales [Net Sales]) / CountRows(Distinct(Sales[LocationDays]))

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

Рис. 10.35. На этот раз все вычисляется корректно

Рис. 10.35. На этот раз все вычисляется корректно

Top