Страницы: 1
RSS
Итоги и промежуточные итоги в сводной таблице с помощью DAX, Корректировка итогов сводной таблицы с помощью функций DAX
 
Ребята, всем привет.
DAX изучаю пару дней.
Поставили задачу суммировать данные за прошлые промежутки и за период 3 месяца назад от текущего.
За прошлые день, месяц, квартал год - проблемы не составило.
А вот за прошедшие три месяца от текущего, пошли проблемы с итогами.
Так как файл с моделями (даже в архиве) превышает 100 кб, приложил только справочники.
Меры ниже вставил текстом:

3MonthAgo:=CALCULATE(SUM('Продажи'[Цена Реализации]);DATESINPERIOD('Календарь'[Date];FIRSTDATE(PARALLELPERIOD('Календарь'[Date];-3;MONTH));+3;MONTH))

II:=IF(HASONEVALUE('Календарь'[Month]);[3MonthAgo];0)

VI:=SUMX(SUMMARIZE('Календарь';'Календарь'[Month]);[II])

VII:=IF(HASONEVALUE('Календарь'[Month])=TRUE();[II];[VI])

Объеденение_мер_:=IF(HASONEVALUE('Календарь'[Month])=TRUE();IF(HASONEVALUE('Календарь'[Month]);[3MonthAgo];0);SUMX(SUMMARIZE('Календарь';'Календарь'[Month]);IF(HASONEVALUE('Календарь'[Month]);[3MonthAgo];0)))

Скрин сводных ниже:
Изменено: Кирилл Авдеев - 16.06.2021 17:19:34
 
Кирилл Авдеев, не могли бы Вы в вашем справочнике сделать модель? Интересно, таки разобраться с этом вопросом
 
Добрый день.
Спасибо за отзывчивость )))
С радостью бы приложил модель, но файл весит 434 КБ, а тут ограничение в 100 КБ.
Архивация не спасает (246 КБ), даже часть данных удалить пробовал и архивировать, 146 КБ получается  :cry:
Поэтому получилось приложить только таблицы с продажами и календарь.
Меры руками прописал, чтобы вставить можно было, а результат сделал в скринах.
Если Вы подскажите каким образом можно это все сжать или оптимизировать, буду благодарен.
 
Цитата
Owlet написал:
Кирилл Авдеев, не могли бы Вы в вашем справочнике сделать модель? Интересно, таки разобраться с этом вопросом
Могу на электронку выслать, но хотелось бы, чтобы решение тут осталось, мало-ли у кого такой же вопрос возникнет.
 
Цитата
Кирилл Авдеев написал:
Архивация не спасает (246 КБ),
вроде как 300 кб можно, пробуйте архивом закинуть
 
Цитата
Кирилл Авдеев написал:
А вот за прошедшие три месяца от текущего,
без примера, по памяти - конструкцию можно такую использовать:
Код
=CALCULATE(CALCULATE(SUM('Продажи'[Цена Реализации]);
     FILTER(ALL(Календарь'[MonthNumber]);Календарь'[MonthNumber]<MAX(Календарь'[MonthNumber])&&
                       Календарь'[MonthNumber]>=MAX(Календарь'[MonthNumber])-3
     )
)

Важное замечание: столбец  "Календарь'[MonthNumber]" должен последовательно считать номера месяцев в календаре, т.е. например янв 2020 - номер 1, то янв 2021 - номер 13. Тогда мера будет считать и пограничные месяцы (за фев - это янв + дек, ноя предыдущего года)
 
О, а вчера писало, что нельзя больше 100 КБ.
Модель прикрепил.
 
Честно говоря, не очень понятно, что Вы хотите получить в конечном итоге. У Вас написаны разные меры,  которые ссылаются друг на друга и Вы еще хотите объединить их. Это как-то на рекурсию больше походит.
По поводу того, что общий итог не выводится, так это логично:
Код
=IF(HASONEVALUE('Календарь'[Month]);[3MonthAgo];0))

У Вас же написано в мерах, что если нет иерархии месяцев, то показать 0. Вот вы его и получили.
По второму вопросу: та же беда " если нет иерархии месяцев (т.е. общий итог), то считать предыдущую меру, а предыдущая ссылается на предпреддыдущую. В такой логике черт  ногу сломит, чтобы разобраться.
Напишите от руки (обычными формулами эксель рядом), какие результаты Вы хотите получить. Мне кажется все решается гораздо более простыми мерами.
 
Есть месяц июнь 2021 года. Хочу проссумировать месяцы март, апрель и май.
Мера которая это делает называется "3MonthAgo".
Но как Вы видите, подитоги в ней не корректны.
Поэтому использовал меру "VI", для выведения корректных подитогов.
Но если сложить подитоги за 2020 и 2021 год, их сумма не сходится с получившимся значением.

Так как в DAX всего третий день, сначало попытался решить задачу частями, а затем объеденить части (меры) в одну, более сложную меру.
Пример решения проблемы пытался взять с сайта: Узнайте, как исправить итоги и субтоталы в DAX (theexcelclub.com)
Изменено: Кирилл Авдеев - 17.06.2021 12:19:29
 
Кирилл Авдеев, Вас не в ту степь понесло, т.е. HASONVALUE служит для других задач. Например, когда мы хотим видеть в одной мере разные методы расчета. Например, если месяц, то считаем сумму, если общий итог, то считаем среднее. Таким способом мы применяем два разных расчета в одном столбце для значений и промежуточных итогов. что касается вашей задачи. Ответ во вложении. Во первых, нужен уникальный идентификатор одного и того же месяца, но разных лет. Прочитайте еще раз
Цитата
Vladimir Chebykin написал:
Важное замечание:
Посмотрите в календаре доп. вычисляемый столбец, который я создал - именно его я использовал в "мера1".
Во-вторых, для познания сущности работы в DAX, Вы начали с очень непростого примера. Вы можете скопировать это решение в боевой файл, но, мне кажется не поймете, как это работает - и это нормально, это сложная задача для начинающего.
П.С. а можете объяснить, для чего применяется такой алгоритм: для каждого месяца посчитать сумму 3-ех предыдущих, а потом все полученные значения просуммировать? Какова практическая цель - я таких задач не встречал на практике.
 
Vladimir Chebykin, получается SUMMARIZE создает таблицу с продажами за 3 месяца, а потом сумма применяется по этой типо новой таблице? так оно работает?
 
Цитата
Owlet написал:
так оно работает?
все верно
 
Цитата
Vladimir Chebykin написал:
Какова практическая цель - я таких задач не встречал на практике.
Владимир, это нужно для подсчета клиентов, которые к нам возвращаются.
Допустим, мы продали 100 сертификатов в феврале. Срок сертификата 3 месяца, значит они должны к нам вернуться в Марте, Апреле или в Мае.
В Июне мы смотрим в каком месяце и сколько клиентов было возвращено, а кто выпал.

По поводу добавленного поля, мне кажется не корректно отображать, в данном отчете, месяц под номером 15 или 23, так как месяцев в году всего 12.
Можете помочь к приведению месяцев к порядковому номеру года?

Когда столкнулся с задачей, начал искать в интернете решение данной проблемы.
В этой статье решалась проблема с итогами и под итогами:
Узнайте, как исправить итоги и субтоталы в DAX (theexcelclub.com)
Вот и решил применить ее к своему алгоритму.
Вы правы, тяжеловато, но как говориться, разведка боем )))
Если Вас не затруднит, можете пояснить, почему в статье отработало, а у меня нет?
Вроде бы создал вложенные меры, как и объяснялось. В разрезе одного года общий итог суммировался, а как добавил второй год (то есть в разрезе пары лет) , промежуточные итоги корректны, а вот общий - нет.
Какую суть (логику) упускаю?
 
Цитата
Кирилл Авдеев написал:
Можете помочь к приведению месяцев к порядковому номеру года?
а вы не пробовали месяц добавить в таблицу?
Цитата
Кирилл Авдеев написал:
Какую суть (логику) упускаю?
если кратко, то весь DAX построен на контексте фильтров: внешних, внутренних. Это большая тема, которую нужно понять для написания правильных мер. Купите книжки или курсы и изучайте, описывать ваше решение бесполезно, оно не верное в корне. Здесь не требуется HASONVALUE, о чем я уже попытался Вам объяснить.
П.С. статью не читал
 
Цитата
Vladimir Chebykin написал:
оно не верное в корне
Заранее прошу меня простить.
Нашел статью, в ней раскрывается проблема, очень похожая на мою. Взял и экстраполировал.
Вы не читая статью, говорите что это в корне не верно.
Мне интересно понять, почему? Почему у человека получилось, а у меня нет. Что я упускаю.
Думал Вы ответите, а Вы предложили другое решение.
За помощь и отзывчивость крайне вам признателен.  :)
Цитата
Vladimir Chebykin написал:
а вы не пробовали месяц добавить в таблицу?
В какую, Продажи?
 
Кирилл Авдеев, в сводной поле поменять на просто номер месяца : 4,5,6 будет
 
Цитата
Кирилл Авдеев написал:
Мне интересно понять, почему?
Потому что Вы неправильно определили, что та статья является целевой для Вас. Я определил с одного взгляда, потому что знаю DAX на уровне "мидла". С чего Вы решили, что Вам нужно менять промежуточные итоги для Вашей задачи? В этом Вы и ошиблись. Ваша задача решается в два этапа:
1) предварительный расчет данных по месяцам (создание виртуальной таблицы с помощью SUMMARIZE)
2) суммирование этих данных.
И где, здесь, простите, требуется менять промежуточные/общие итоги? Нужно лишь составить правильный алгоритм, а чтобы его составить, нужно разбирать контекст внешних фильтров. Я Вам итак много информации дал к размышлению, но складывается такое ощущение, что Вы лучше знаете как надо делать... Успехов в погружение DAX.
 
Цитата
Vladimir Chebykin написал:
С чего Вы решили, что Вам нужно менять промежуточные итоги для Вашей задачи?
Владимир, мышление стандартного дилетанта: не сходятся итоги, следовательно нужно их изменить. Вот и пошел в лоб решать задачу, упустив суть.
В том то и проблема, не знал как делать.
Цитата
Vladimir Chebykin написал:
1) предварительный расчет данных по месяцам (создание виртуальной таблицы с помощью SUMMARIZE)2) суммирование этих данных.
Согласен, этот подход правильный.
Осталось понять в каких ситуациях использовать ту статью. Думаю в DAX у меня еще долгий путь.
Ни в коем случае не хотел Вас обидеть или поставить под сомнения Ваши компетентности.
Спасибо огромное за вектор и ход мышления.
 
Цитата
Кирилл Авдеев написал:
Осталось понять в каких ситуациях использовать ту статью.
я же писал
Цитата
Vladimir Chebykin написал:
HASONVALUE служит для других задач. Например, когда мы хотим видеть в одной мере разные методы расчета. Например, если месяц, то считаем сумму, если общий итог, то считаем среднее. Таким способом мы применяем два разных расчета в одном столбце для значений и промежуточных итогов
 
Кирилл Авдеев, могу посоветовать одно - покупаем книгу (можно в pdf, чтобы не ждать доставки) и через пару недель такие простые задачи Вы решаете без проблем;)
Страницы: 1
Наверх