Страницы: 1
RSS
Расчет точности прогнозирования Power Pivot c созданием меры
 
Доброго времени суток, уважаемые.

Поставили мне на работе задачу рассчитать точность прогнозирования продаж, пришлось использовать Power Pivot т.к. входящая информация тянется из SQL таблиц и OLAP кубов. Решил опробовать алгоритм на самом простом примере, вроде бы получилось, но остается сомнение, что можно было сделать это проще.

Подскажите пожалуйста, если кто-то сталкивался с подобной задачей, есть ли более простые и короткие пути решения?

Итак, по моему примеру.
На листе Data есть табличка с прогнозом, и отдельная с продажами + есть табличка с расчетом точности (для сверки с результатом в Power Pivot).

На листе Pivot имею таблицу созданную по модели данных: столбец с прогнозом, продажами, далее идет столбец "FA" с точностью прогноза по формуле "1-ABS(Прогноз-Продажи)/Продажи", вроде бы мера простая и точность рассчитывает, однако, если взглянуть на итоги и подитоги, то станет ясно, что они неверны.

Чтобы получить верные итоги по средневзвешенной мне пришлось создать несколько промежуточных мер. Можно ли получить результат более изящным методом?

Через промежуточные меры результата я все-таки добился, но проблема в том, что в Power Pivot у меня грузится почти 7 млн строк с прогнозом, + еще факт, + справочники, соответственно, созданная таблица на основе модели данных и мер очень долго грузится и с ней очень тяжело работать, потому я и ищу возможные пути оптимизации меры для расчета точности.

Если вдруг у вас есть советы как еще можно ускорить работу с таблицей, буду безмерно благодарен. Извините за кучу текста))    
 
Доброе время суток
Цитата
dimacrut написал:
то станет ясно, что они неверны.
Чем не верны? Продажи 483, предсказание 444 - чем не верна точность 91,2%?
Во вторых, путь есть два числа А - реальность, В - прогноз. Тогда, что такое 100% точный прогноз и что такое 0% точный прогноз? Как бы получаемый -180%  точность прогноза не имеет смысла, на мой взгляд. Или это как-то интерпретируется. Я бы всё же предложил 1 - Abs(A - B) / Max(A, B), тогда бы это было где то в интервале 0% - 100%.
Третье, если вы вводите понятие средневзвешенное, то может стоит указать - что вы принимаете за вес?
Или вы ожидаете только коллег по профессиональному направлению - тогда стоит ли задавать вопросы на форуме по Excel? А вдруг они тут не хотят?
 
Андрей, здравствуйте.

1. Неверна точность хотя бы на примере "Сеть 2". По этой сети видим точность 100% т.е. как бы прогноз = факту (по тоталу), но на уровне товаров имеется разница.
2. Подразумевается, что 100% это идеальный вариант, когда прогноз = факту, 0% - это самый негативный результат. Да, - 180% сложно как-то объяснить, потому в столбце "Result FA" я "зануляю" отрицательный результат, но уход в еще больший "-" будет влиять на строку итогов.
3. По поводу формулы 1 - Abs(A - B) / Max(A, B) я полностью согласен, однако, руководство просит именно с фактом в знаменателе. Хозяин - барин...
4. За вес беру факт.
5. Да, я понимаю, что ушел в сторону от вопросов по Excel, но нужно было как-то объяснить где и как я использую средневзвешенное значение. Но вопрос все-таки по Excel - как рассчитать средневзвешенное значение в DAX. А вдруг кто-то все же сталкивался с подобной задачей  
 
Цитата
dimacrut написал:
А вдруг кто-то все же сталкивался с подобной задачей
Сталкивались. Метеорит и цунами :)
Никакой прогноз не может быть достоверным при недостаточных исходных данных. Можно только предположить о результате...
Цитата
dimacrut написал:
По этой сети видим точность 100%
100% достоверные данные? ;)
Бегите от работодателя, который от  Вас требует 100% прогноз :)
Или продолжайте корректировать строку итогов, если это удовлетворяет и Вас, и работодателя :)
Извините, просто мысли вслух - Excel не при чем.
Цитата
dimacrut написал:
180% сложно как-то объяснить
Поэтому
Цитата
Андрей VG написал:
точность прогноза не имеет смысла
Попробуйте более конкретней объяснить что откуда брать и что нужно получить
или
Цитата
ускорить работу с таблицей,
? :)
Изменено: _Igor_61 - 04.10.2021 21:24:43
 
Цитата
dimacrut написал:
Да, - 180% сложно как-то объяснить, потому в столбце "Result FA" я "зануляю"
То есть идеи собственно методики расчётов получить не получиться? Да, если продажи в два и более раза меньше чем прогноз вы обнуляете. Но, если продажи в два раза больше чем прогноз, то ваша формула даёт точность прогноза в 50%. Тем самым вы завышаете качество прогноза :)   Подтасовка?
Опять же использование количества продаж в качестве веса, вы так и не объяснили. Хорошо, вопрос пусть вы спрогнозировали продать 10000 коробков спичек и продали 10000. А перстней с брильянтами 10, но продали только 4. Тогда по вашей логике получается, что точность прогноза - 100%. Ура, все в шоколаде!
Версия меры
Код
=Var weightSum = SUMX(GROUPBY('FCS'; 'СетьСпр'[Сеть]; 'ТоварСпр'[Товар]);
  Var fcsSum = CALCULATE([FCS_sum])
  Var saleSum = CALCULATE([Sales Total])
  Var cf = 1 - DIVIDE(ABS(fcsSum - saleSum); saleSum; BLANK())
  Return  saleSum * cf
)
Var result = DIVIDE(weightSum; [Sales Total]; BLANK())
Return IF(result < 0; 0; result)
Будет ли быстрее.. У меня нет ваших объёмов данных.
 
Цитата
Андрей VG написал:
А перстней с брильянтами 10, но продали только 4
Я с предыдущим своим аналитиком разбирал как-то прогноз, он мне доказывал, что в общем у него норм точность. Полезли ковыряться, карданов установили на 40 штук больше за месяц, цилиндров - аналогично, зато незамерзайки спрогнозировали 500 баклажек, а потратили 350, вот она все и перекрыла!  :D
Прогноз штука тонкая!
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Прогноз штука тонкая!
Естественно, иначе всё как у Марк Твена... Думать надо, а не механически использовать первые нагугленные решения без разбора :)
 
Андрей VG, спасибо за меру, все работает, скорость работы еще буду тестить. Очень вам благодарен.
По поводу подтасовки))) Да я согласен, что формула 1 - Abs(A - B) / Max(A, B) является более логичной, но от руководителя я слышу что-то типа "мне не нравится, что в одном случае в знаменателе будет факт, а в другом случае прогноз"........ Кстати, в предыдущей компании мы как раз использовали формулу с MAX и не знали бед, не было этой ерунды с занулением и прочего бреда.

Цитата
Андрей VG написал: спрогнозировали продать 10000 коробков спичек и продали 10000. А перстней с брильянтами 10, но продали только 4. Тогда по вашей логике получается, что точность прогноза - 100%.
По поводу спичек и перстней))) Была у нас похожая история когда продавался основной товар в кол-ве 200-300 шт/мес, а также всякая мелочевка к этому товару типа болты да гайки, которые продавались в месяц тысячами. По вышеописанной логике нам было бы достаточно хорошо спрогнозировать болты и гайки, а на основной товар забить и все было бы в шоколаде. Мы разделяли товар по схожим группам, в результате у основного товара была своя точность, у мелочи своя и эта мелочь не искажала результат основных групп.  
Изменено: vikttur - 11.10.2021 22:43:36
Страницы: 1
Наверх