Страницы: 1
RSS
ABC в Power Pivot (25 млн строк и 4 фильтра). Добавить вычисление АВС анализа в отдельный столбец
 
Всем добрый день.
Обращаюсь в вам за помощью, так как в прошлой теме, созданной мной, год назад очень помогли, спасибо.

У меня есть модель данных на 25 млн. строк. В файле приложении я, естественно, сократил все до 30 строк.
Мне нужно добавить в неё вычисление АВС анализа в отдельный столбец (не мера).
Проблема в том, что АВС нужно сделать по сумме продаж по столбцу Sellps_2, но с несколькими, скажем так, фильтрами:
1. В разрезе месяца - столбец date_2 (в полной модели данных данные по-месячно с 2014 года)
2. Маркет - столбец Market (всего 4 магазина - SVR,SFR,SVN,SFL)
3. По поставщику - столбец Supply
4. По отделу (всего порядка 10 отделов и поставщик может возить товар в несколько отделов)
5. Соответственно артикул, которому присуждается категория А,В или С в зависимости от продаж 80,15,5%

На сколько реально сделать рабочую формулу, чтобы добавить столбец вычисляемый А,В,С? Просто я пробовал несколько
вариантов и таблица просто висла....

Ссылка на файл (не грузит более 100 кб на форум):
Изменено: Gerzzog - 06.04.2021 11:13:14
 
Gerzzog, Добрый день! В моделе нет никаких справочников (товаров, календарь, поставщики) ? А нужно бы.
Не могли бы вы желаемый результат в табличке представить?
 
Отдельно нет справочников( просто у меня есть папка с данными помесячно и я загружаю из через power query в модель данных.
По сути есть основные столбцы - art (номер товара, артикул), сумма продаж Sellps_2, поставщик Supply, маркет (4 магазина - SVR,SFR,SVN,SFL), дата - date_2, отдел - Otd (сразу после даты)

А желаемый результат, это просто отдельный столбец в конце этой таблицы с перечнем групп А.В.С
Изменено: Gerzzog - 06.04.2021 11:11:16
 
Цитата
Gerzzog написал:
не грузит более 100 кб на форум
Во-первых, не 100, а 300Кб. Архивируйте файл до 300 и выкладывайте на форум, на облака не у всех доступ имеется.
Во-вторых, вам нужен динамический или статический расчет?
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Во-вторых, вам нужен динамический или статический расчет?
Статический расчет
Спасибо, заархивировал. Прикрепил файл
 
а почему мерой-то нельзя? ведь создание вычисляемого столбца в модели данных на 25 млн строк - даже звучит страшно

а вообще про ABC очень толково написано вот: https://www.daxpatterns.com/abc-classification/
Изменено: ArgentumTiger_7 - 06.04.2021 12:43:44
 
Цитата
ArgentumTiger_7 написал:
а почему мерой-то нельзя
Я так понял, что добавление столбца (статическое исчисление) будет в конечном итоге быстрее просчитываться, чем Мера. Возможно я не прав
+ АВС анализ мне нужен как этап просчета. Потом я буду выводить данные по отсутствию товара группы А (если его нет на остатке или товарный запас меньше 3 дней)
Изменено: Gerzzog - 06.04.2021 14:44:56
 
Gerzzog, я пытался внедрить решения по АБС анализу по примерам интернета на массиве данных порядка 5 млн. строк. Так вот ничем успешным это не увенчалось, excel просто вис
 
бездумная копия - не цитата [МОДЕРАТОР]

ок, спасибо за инфо. Я так же подозреваю, что вряд ли. Но мало ли) вдруг у кого-то есть решение, которое мне поможет
 
Gerzzog, вы таки по ссылке от Серебряного Тигра за нумером 7 проходили? На вашем датасете реализовать пробовали?
Вот горшок пустой, он предмет простой...
 
Да, я пробовал по этому варианту, но, наверное, скилов не хватает мне применить на моем примере (тут аж 4 фильтра по сути). Не получалась формула.
Честно говоря, по этой статье я больше пытался делать по Dynamic ABC classification.
 
Gerzzog, как раз динамический на таком массиве без промежуточных таблиц точно не взлетит.
Вот горшок пустой, он предмет простой...
 
Поэтому хочу попробовать статический, но пока нет успехов( если с формулой поможете, буду очень признателен. Не получается коммулятивную сумму,
по многим критериям получить
 
Вам для ваших целей нужно использовать вариант Snapshot ABC classification из ссылки выше. Тогда все будет работать относительно быстро.
Вот только меру из этой статьи для вызова классификации придется переделывать, т.к. функции TREATAS в Экселе нема. Но это вопрос решаемый, главное чтобы вы поняли как сформировать таблицу-снимок для расчета классификации.
Вот горшок пустой, он предмет простой...
 
Там просто только 2 классификации....а у меня аж 5 получается((( нет случайно примера для хотя бы 3 критериев, чтобы понять как их добавлять в формулы?
 
Gerzzog, вам нужно сначала в PQ создать таблицу с декартовым произведением всех возможных критериев, после чего к ней уже добавлять расчетные столбцы в DAX. Формулы там в общем-то те же что и по ссылке.
Вот горшок пустой, он предмет простой...
 
На том же дакспаттерне можно скачать файлы в Эксель ) по-моему, там TREATAS заменён на INTERSECT
 
Цитата
PooHkrd написал:
т.к. функции TREATAS в Экселе нема
Привет, Алексей.
А зачем она в данном случае? Насколько понимаю, нужно оценить данные по артикулам при равных Supply, Market, date_2, Otd.
Код
= Var groupFilter = CALCULATETABLE('abc'; ALLEXCEPT('abc'; 'abc'[date_2]; abc[Market]; abc[Otd]; abc[Supply]))
Var groupSum = CALCULATE(SUM('abc'[Sellps_2,]); groupFilter)
Var curVal = 'abc'[Sellps_2,]
Var moreOrEqualSum = CALCULATE(SUM('abc'[Sellps_2,]); FILTER(groupFilter; 'abc'[Sellps_2,] >= curVal))
Var pct = moreOrEqualSum / groupSum
Return SWITCH(TRUE();
   pct <= 0,8; "A";
   pct <= 0,95; "B";
   "C"
)
Или я чего-то со своей дремучестью не улавливаю важного?
P. S. Да понимаю, что у ТС внезапно засунута первичка в приделах равных Supply, Market, date_2, Otd есть несколько записей по артикулам, ну так, в принципе суммарно обобщить до сумм по артикулу в виртуальную таблицу, а дальше уже по ней.
Изменено: Андрей VG - 06.04.2021 23:50:51
 
Андрей VG, спасибо вам большое. А подскажите, если вылетает такая ошибка "Столбец "Sellps_2," в таблице "abc" не удается обнаружить, или его не удается использовать в этом выражении." Что может быть не то?
Попробовал эту формулу и в меру подставить и в отдельный столбец

Все заработало. Там просто запятая стояла.  Сейчас буду тестить на основной таблице

P.S.
пока что 30 минут идет вычисление результатов:) еще не завершилось. Чувствую, mechanix 85 был прав и вариант для большой таблицы не подходит(((
 
Gerzzog, я делал ABC анализ совмещая power pivot и формулы excel. Исходные данные о продажах брались с модели данных (фильтровались срезами - период, категории товаров и т.д.) предварительно отсортировав по убыванию продаж, а сам расчет ABC производил через формулы. Формулы хоть и подтормаживали на минуту-две для вычислений, но это лучше чем ничего

Оранжевые фильтры - фильтруют модель данных, синие - таблицу по АВС анализу
Изменено: mechanix 85 - 07.04.2021 09:43:37
 
mechanix 85, ну, 25кк строк то через Эксель не пропустишь. Поэтому я и написал ТСу что самый оптимальный вариант для его массива это снапшот. Он конечно отожрет место в модели под таблицу-снимок, но зато сожрет не так много оперативки и при этом довольно быстро можно будет вытащить полученные значения в столбец, либо в меру.
Вот горшок пустой, он предмет простой...
 
PooHkrd, у меня справочник товаров 87К, из них с продажами порядка 30К, не думаю, что на массиве 25КК будет столько же уникальных товаров
 
mechanix 85, ну, то есть у вас роль снапшота выполняет Эксель, а это лишний гемор, обновил данные, подгрузил новые данные на лист, пересчитал группы, залил новые группы в модель. Снапшот решает данный вопрос на лету внутри модели, без этих лишних телодвижений.
Вот горшок пустой, он предмет простой...
 
у меня уникальных товаров порядка 250-300 000. По этому буду пробовать подружиться с снапшотом(
но меня пугает, что не смогу написать формулы на целых 4 фильтра (Supply, Market, date_2, Otd). В примере снапшота только один - год
 
Оживлю немного старую тему, может кому пригодиться эта информация. В общем формула Андрей VG, которую привели в постах выше работает отлично, но на объеме информации до 1 млн строк в определенном периоде.
Решение вижу в подтягивании группы А,В,С на файл исходника за месяц и уже потом соберу модель данных из папки с исходниками помесячно.
Мне согласился коллега помочь с макросом по заполнению групп А,В,С по несколько критериям (нужно будет в исходнике отфильтровать отдел). Когда
будет готов макрос, выложу сюда, может кому-то пригодиться. Планируется сделать через CreateObject("Scripting.Dictionary"), так как "топорные" решения на массиве данных под миллион строк работают слишком долго.
 
Gerzzog, когда столько уникальных товаров, то конечно могут быть проблемы, но тут значит надо найти подходящую "железку".
И не в обиду уважаемому Андрей VG, в его формуле очень ресурсоемкий самый первый шаг
Код
CALCULATETABLE (
    'abc',
    ALLEXCEPT ( 'abc', 'abc'[date_2], abc[Market], abc[Otd], abc[Supply] )
)

который возвращает таблицу со всеми столбцами
Вообще конечно у меня тут главный вопрос: вы уверены, что вам для ABC анализа нужны все 30 столбцов? может стоит оставить только то, что действительно нужно, ну и сделать все таки модель звезду со справочниками?
Ну и как вариант попробовать следующую формулу:
Код
=var _cur=abc[Sellps_2]
var _group=CALCULATE(SUM(abc[Sellps_2]);ALLEXCEPT(abc;abc[date_2];abc[Market];abc[Supply];abc[Otd]))
var _total=CALCULATE(SUM(abc[Sellps_2]);ALLEXCEPT(abc;abc[date_2];abc[Market];abc[Supply];abc[Otd]);abc[Sellps_2]>=_cur)
var _abcNum = DIVIDE(_total;_group)
return
SWITCH(TRUE();
_abcNum<=0,8;"A";
_abcNum<=0,95;"B";
"C")
)
 
В общей не получилось с макросом, по этому сделали все посредством SQL запроса. Программисты дали доступ к базе данных.
Теперь все выгружаю уже с АВС анализом в модель данных
Практика показала, что от 1 млн строк уже с трудом справляется PP и файл тормозит.
Пример, кода, может кому пригодиться АВС анализа:
Код
abc = case
   when 100. * cumtotal / nullif(s.total,0) = Null then 'C'
   when 100. * cumtotal / nullif(s.total,0) < 5 then 'C'
   when 100. * cumtotal / nullif(s.total,0) < 20 then 'B'
    else 'A' end
FROM 
(
   select dep_code, s.market_id, amt, date_start,
   cumtotal        = sum(s.amt) over (partition by date_start, dep_code, s.market_id order by amt rows between unbounded preceding and current row),
   total           = sum(s.amt) over (partition by date_start, dep_code, s.market_id),
      
from #table
) S
Изменено: Gerzzog - 14.09.2021 09:30:03
Страницы: 1
Наверх