При создании мер DAX не используйте метод «фильтровать, а затем вычислять»
Предположим, что нужно создать меру DAX для функции SUM (Sis [Sales] ). Перед началом вычислений, выполняемых надстройкой PowerPivot, задействуются все заранее настроенные фильтры. Для подсчета значения 851, отображаемого в ячейке D6 (рис. 10.36), программа выбирает данные, соответствующие условиям Еер=Билл, Date=6/2/2011.
Рис. 10.36. Для вычисления значения 851 Excel применяет заранее настроенный фильтр
Если в вычислениях DAX фильтр не используется, значение в выделенной ячейке (рис. 10.36) представляет собой результат суммирования строк, выделенных стрелками на рис. 10.37. Обратите внимание на то, что формула в ячейке Е12, показанной на рис. 10.37, имеет вид =СУММЕСЛИМН(Sis[Sales],Sls[Rep],»Билл»,Sis[Date],40696) и тоже возвращает значение 851. Формула DAX, имеющая вид =SUM(Sis [Sales]), немного короче.
Рис. 10.37. Для получения результата, показанного на рис. 10.36. объединяются строки, обозначенные стрелками
Наверное, вы уже заметили, что при выполнении большинства вычислений не нужно определять фильтр. Это предположение будет неверным в случае, когда один из компонентов формулы нужно применить ко всем строкам, а не только к отфильтрованным.
Предположим, что нужно узнать, какую часть составляют продажи видеорегистратор за несколько дней по отношению к продажам за месяц. В этом случае нужно составить дробь. Числитель дроби представляет собой выражение SUM (Sis [Sales]), а знаменатель — сумму по всем записям в таблице продаж (Sales). Вычисление знаменателя может представлять собой довольно трудную задачу.
В DAX вместо функции СУММЕСЛИМН (SUMIFS) применяется функция Calculate. Для определения этой функции требуется задать выражение и один либо несколько фильтров. При определении этих фильтров применяется специальная функция ALL. В случае определения выражения Calculate (Sum(Sls [Sales] ) ,ALL(Sls)) фильтр превращается в своего рода «антифильтр». Вместо ограничения области вычислений ключевое слово ALL указывает на то, что вы хотите видеть не только продажи Билла за 2.6.2011, а всю сумму по продажам, указанным в таблице.
На рис. 10.38 показана новая мера, применяемая для вычисления процента от общего итога по продажам с помощью такой формулы: =SUM(Sls[Sales])/Calculate(Sum(Sis[Sales]),All(Sis)).
Рис. 10.38. Вычислить знаменатель в формуле — задача не из легких
Процент от общего итога, отображаемый в ячейке F7, свидетельствует о том, что продажи торгового представителя Билла 2 июня 2011 года составили 851 долл., что соответствует 0,9% от общего итога по всем продажам.
Рассмотренный выше пример является довольно простым, поскольку использовалась функция, вычисляющая процент от общего итога. Но это не повод, чтобы отказаться от исследования синтаксиса функции all (sis). Разобравшись с синтаксисом, вы сможете заменить первый аргумент в выражении Calculate функцией Max, Min, Average либо другой подобной функцией. В результате поведение функции Calculate станет напоминать поведение функции суммеслимн, СРЗНАЧЕСЛИМН И Т.Д.
Во времена появления бета-версии надстройки PowerPivot многие пользователи описывали примеры вычисления процента от общего итога по продажам, используя функции Calculate либо All. И эти примеры были одними из самых простых примеров вычисляемых мер.
Предположим, что требуется вычислить, какую долю занимает сумма по продажам, равная 851 долл., торгового представителя по имени Билл по отношению ко всем продажам, совершенным 2 июня 2011 года. Числитель меры DAX будет иметь вид =Sum(Sls[Sales]). Вычисление знаменателя, опять же, будет самой трудной задачей.
При вычислении знаменателя применяется следующая ключевая фраза: «Учитывать все продажи, датированные сегодняшним днем, не учитывая наличие фильтра по торговым представителям. Дайте мне сведения по продажам, имеющие отношение ко всем торговым представителям». Если воспользоваться выражением ALL (Sis), в процессе вычисления будут отброшены все фильтры. Поэтому лучше обратиться к выражению AllExcept {Sis,Sis[Date]).
Я не считаю, что это выражение является некорректным, просто оно не совсем очевидно. Понимайте его следующим образом: «DAX всегда выполняет фильтрацию по дате и торговым представителям. Откажитесь от всех фильтров, за исключением фильтра по дате». Как показано на рис. 10.39, мера DAX вычисляет следующее: =SUM(Sls[Sales])/Calculate(Sum(Sis[Sales]),AllExcept(Sis,Sis[Date])). В результате применения этой функции отображается ежедневный процент продаж каждого торгового представителя.
Рис. 10.39. Функция All Except определяет игнорирование всех фильтров, за исключением фильтра по дате
Можно также переопределить используемые фильтры путем указания других фильтров для функции Calculate. Синтаксис функции Calculate выглядит следующим образом: Calculate(выражение,[фильтр 1],[фильтр 2],[фильтр 3], [фильтр 4], .. .)
Как и в случае с функцией СУММЕСЛИМН в Excel 2010, в данном случае можно определять дополнительные фильтры. Например, в результате применения выражения =Calculate(Sis[Sales], Sis Rep] =»Эмбер») отображается сумма по всем продажам для торгового представителя Эмбер в текущей строке. Если Эмбер носит гордое звание чемпиона продаж в вашем магазине, найдутся восторженные поклонники, которые захотят подсчитать свой процент от суммы продаж Эмбера.
Формула =SUM(Sis[Sales])/Calculate(Sis[Sales],Sis[Rep]= «Эмбер») отображает процент продаж данного торгового представителя от показателей Эмбера для данного дня. Обратите внимание на отчет о продажах, отправленный менеджером магазина 16 июня (рис. 10.40). Теперь Крис может позвонить домой и сообщить радостную новость: «Ликуйте! Я продал товаров на сумму, которая составляет 259,2% от уровня продаж Эмбера».
Рис. 10.40. Формулу, вычисляющую проценты всех торговых представителей от уровня продаж Эмбера, можно создать с помощью языка формул DAX