Ребята, доброе время суток всем. В работе с АВС анализом столкнулся с некоторой сложностью, а именно подсчетом нарастающей суммы. В интернете есть примеры, однако они все связаны датой. В данном случае нет дат.
Необходимо просуммировать Долю по Клиентам, желтый блок скрина (или файл в Excel).
Видел этот пример. Проблема с EARLIER, не могу пройти тот этап поскольку выплывает ошибка. Отчасти это связано, видимо с незнанием самой функции, использовал ее впервые, изучаю, отчасти, возможно, сам мой кейс несколько иной.
В какой форме следует использовать этот EARLIER, помогите тогда в плане корректного применения.
Ну, если взять формулу для вычисляемого столбца прямо из статьи:
Код
[CumulatedSales] =
CALCULATE (
SUM ( Products[ProductSales] ),
ALL ( Products ),
Products[ProductSales] >= EARLIER ( Products[ProductSales] )
)
здесь EARLIER используется в качестве фильтра Products[ProductSales] - это ссылка на столбец EARLIER ( Products[ProductSales] ) - это ссылка на значение столбца в текущей строке ( так функция работает только при создании вычисляемых столбцов - в мерах все несколько сложнее). Т.е. образно функция сортирует столбец по убыванию и начинает перебирать строки по очереди суммируя все значения, которые находятся выше плюс значение в текущей строке. Тут весь вопрос в том, что для расчета накопительной суммы вам в любом случае нужно задать очередность перебора строк. Где-то это задается датой, где-то индексом, здесь сортировкой.
Вы можете смоделировать корректную формулу в файле Example.xlsx, который был приложен? Теорию я вижу, но практика не позволяет мне этого сделать. В различных вариациях выдает
Андрей VG, PooHkrd, В любом переборе вводных для EARLIER мера при проверки показывает ошибку. Использовал как обращение к мерам, так и к столбцам. Вроде бы и тупой кейс, но не могу его решить. Гляньте пожалуйста.
Честно спер отсюда Если перевести по-простому, то для использования функции внутри меры нужно сначала создать внешний контекст (что достигается при помощи ALL('Клиенты') ), тогда появляется внутренний контекст строки в сводной таблице к которому и обращается EARLIER. Когда же вы пишите формулу для столбца, то этот контекст имеется по-умолчанию. Если что не так сказал, то, думаю, Андрей меня поправит.
Имейте ввиду, что данная формула накопительно суммирует значения объемов от большего к меньшему, что не обязательно будет совпадать с сортировкой клиентов в вашей сводной таблице. Т.е. итоговый результат в сводной вас может слегка расстроить
Также можно посчитать аналогичные результаты по варианту "В", когда заходим с ранжировкой RANKX Клиентов по объему. Далее, через SUMX и TOPN по RANKX с ALL Клиентами суммируем объемы по нарастающей. В конце находим накопительный процент отношением строки два и один выше.
В этом случае можно оперировать исключительно мерами, что, как мне кажется, большой плюс. В качестве изюминки выключил итоги через IF (ISFILTERED), в данном случае это всегда лишнее.
Для себя получил хороший заряд бодрости и радости от того, что добил этот пробел в опыте с DAX. Часто делаем АВС-XYZ, понимание в этой части очень было важно.
PooHkrd написал: создать внешний контекст (что достигается при помощи ALL('Клиенты') )
неправильно. Нужен не просто внешний контекст, а внешний контекст сроки (row context), который создается FILTER. И вот этот FILTER задает как раз наружный строковый, который и читается EARLIER. SUMX создает внутренний (и что самое любопытное, он будет равен одной строке наружного строкового контекста), который EARLIER игнорирует
Попробую сформулировать тоже самое, но как-то на пальцах: Функция используется как выражение внутри второй функции FILTER, значит этот фильтр не создает для неё контекста т.е. EARLIER "выглядывает" за этот фильтр и видит там SUMX, который как раз задает контекст строки, таким образом она игнорирует именно этот заданный контекст и видит только контекст, который задан первым фильтром с ALL и применяет этот контекст к столбцу. Соответственно если использовать EARLIEST то он проигнорирует все фильтры и будет использовать только те контексты, которые задаются самой сводной таблицей? Так?
Итератор типа FILTER или SUMX создает строковый контекст для своего второго аргумента. EARLIER находится во втором аргументе внутреннего FILTER, следовательно внутренний FILTER создает первый строковый контекст для него. EARLIER игнорирует (или, как вы пишете, "выглядывает за") первый строковый контекст, созданный внутренним FILTER. Но так как этот внутренний FILTER сам по себе является первым аргументом SUMX, то сам SUMX еще не успел создать никакого строкового контекста (он тоже создает его для второго аргумента). Поэтому EARLIER ищет строковый контекст снаружи относительно SUMX, и находит его в наружном FILTER, который ALL('Клиенты'). Находит, берет объем клиента из ALL(Клиенты). Сверяет его с тем объемом, который задан посредством внутреннего FILTER. А внутренний FILTER перебирает табличку из 1 строки (так как он получил ее в контексте из сводной). Т.е. фактически это текущий клиент сводной. Сравниваем полученное из внешнего filter с текущим клиентом сводной, если больше, то считаем число. А если меньше - то у нас получится BLANK Итого внешний FILTER для каждого клиента получит или число (что равно TRUE) или BLANK (что равно FALSE), и так и отберет нужных клиентов. А потом уже посчитает для них сумму наконец. И, кстати, вместо SUMX может быть MAXX, и так далее. Не важно, что, важно то, что получится в предыдущем абзаце.
А вообще эта мера очень громоздкая и прям перебор с количеством строковых контекстов. Достаточно двух:
Есть более элегантный вариант, без EARLIER, и строковый контекст лучше изучать на таком примере кумулятивной меры:
Код
Total Value. tn = SUM ( 'Клиенты'[Объем] )
Cumulative Value =
CALCULATE (
[Total Value. tn];
FILTER (
ALL ( 'Клиенты' );
[Total Value, tn] >= SUM ( 'Клиенты'[Объем] )
)
)
ЗЫ и итог убирать не надо
Цитата
PooHkrd написал: если использовать EARLIEST то он проигнорирует все фильтры и будет использовать только те контексты, которые задаются самой сводной таблицей?
а проверьте. Нет В мерах он будет брать самый наружный заданный формулой контекст. В вычисляемом столбце - контекст строки таблицы, неважно, сколько вложенных строковых контекстов есть в формуле столбца.
Ваша мера отлично работает, но у меня есть проблема. У меня довольно объемная таблица - около 3,4 млн строк, и справочник примерно на 100 тыс. строк. Когда запускаю расчет АБС - система напрочь подвисает, и показывает нехватку памяти (памяти 16 ГБ).
При этом, если ставить фильтр на ТОП наименований - все работает. Я пробовал несколько вариантов мер, Вашу, и с сортировкой по рейтингу - ничего не помогает. Это странно, потому что в обычной сводной накопленный процент считает мгновенно для этой же базы.
Kra65, чтобы видеть что не так - нужно видеть модель со всеми связями. Не обязательно со всеми 3.4 млн строк, но таблички нужно видеть все и связи между ними тоже.
как минимум - покажите меру. Если она использует другие меры, то их формулы тоже желательно - может быть, вся история там. Ну и понятно, что 100 тыс. в справочнике - не мало. Но тут еще второй важный вопрос: а вам точно нужен динамический подсчет нарастающего итога, или статичного достаточно?
вообще EARLIER и RANKX какими-то неоптимальными мне кажутся (сравнивая построчно)... если понятна бух.логика - то вариант я рассматривала здесь ... а у вас, насколько понимаю, ещё проще - только кредит считаете - по сути... [суммой накопительной до данной даты - остатки и ваш кредит]... оптимизируйте (я ведь делала 1-й раз и не хочу опять лезть в PP)... ну если сможете перевести всё в данные бух термины... и подрядить бух логику ведения счёта... условно интерполируя на ваши объекты расчётов... - то и без EARLIER справитесь - она вроде тормозит!!... но ,бух логикой, действительно, проникнуться надо - на пальцах не буду объяснять (а в примере показала в той ветке)... Начальное сальдо+Дебет-Кредит=Конечное сальдо ... и всё с Фильтром по Товару... - это по сути переводится, как там сказал Андрей VG
Цитата
Андрей VG написал: банальной задачи к текущей дате посчитать сумму приходов минус сумма расходов плюс начальный остаток, начинаются танцы с бубном
только без бубна ... и у вас даже дебет не нужен... а может ещё и остатки не нужны... (вечно путаю ABC и XYZ анализ) p.s. или вот соседняя ветка уже сейчас - Касса в сводной таблице (Power pivot) - честно говоря не смотрела ещё...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
«Когда запускаю расчет АБС - система напрочь подвисает, и показывает нехватку памяти (памяти 16 ГБ).»
Производительность функции EARLIER может снижаться, поскольку теоретически может понадобиться выполнить количество вычислений, близкое к общему количеству строк (в столбце), умноженному на это количество (в зависимости от синтаксиса выражения). Например, если столбец содержит 10 строк, то может потребоваться до 100 операций, а для 100 строк может выполняться до 10 000 операций.