Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Piviot распределение остатка между магазинами учитывая их потребность и рейтинг
 
#1
27.07.2021 13:37:04
Добрый день!! Помогите плз разобраться. Есть список магазинов отранжированных по рейтингу, есть из потребность в подсортировке по артикулам. Есть доступный остаток к распределению. Необходимо распределить этот остаток между магазинами исходя из рейтинга, учитывая потребность
Изменено: Алена - 27.07.2021 13:39:36
 
 
 
#2
27.07.2021 15:29:46
Алена, если приоритет магазина уникален (нет нескольких магазинов с одним приоритетом), то вычисляемый столбец в PP:
Код
РаспределениеDAX = 
VAR CurPriority = 'Потребность'[Рейтинг магазина] 
VAR CurCode = 'Потребность'[артикул] 
VAR CurNeed = - 'Потребность'[Потребность] 
VAR Available = RELATED ( 'Доступность'[в доступности к распределению] ) 
VAR MorePriority = FILTER ( 'Потребность'; 'Потребность'[артикул] = CurCode && 'Потребность'[Рейтинг магазина] < CurPriority ) 
VAR ResMorePriority = MIN ( SUMX ( MorePriority; - 'Потребность'[Потребность] ); Available ) 
VAR CurRes = MIN ( CurNeed;  Available - ResMorePriority ) 

RETURN CurRes

Если несколько магазинов могут иметь один приоритет и при нехватке всем одноприоритетным магазинам нужно им частично распределить пропорционально потребности, то в PQ:
Код
let
    src = Потребность,
    transform = Table.TransformColumns(src, {"Потребность", (Потребность)=>-Потребность, Int64.Type}),
    join = Table.NestedJoin(transform, {"артикул"}, Доступность, {"артикул"}, "Доступность", JoinKind.LeftOuter),
    expand = Table.ExpandTableColumn(join, "Доступность", {"в доступности к распределению"}, {"Доступность"}),
    groupRating = Table.Group(expand, {"Рейтинг магазина", "артикул"}, {{"Потребность", each List.Sum([Потребность]), type nullable number}, {"Доступность", each List.Average([Доступность]), type nullable number}}),
    filter = Table.SelectRows(groupRating, each [Потребность] > 0),
    calcRating = Table.Group(filter, {"артикул"}, {{"tmp", РаспределениеПоПриоритетам, type table [Рейтинг магазина=Int64.Type, Можно распределить=Int64.Type]}}),
    expandcalcRating = Table.ExpandTableColumn(calcRating[[tmp]],"tmp",{"Рейтинг магазина", "артикул", "Можно распределить"}),
    join2 = Table.NestedJoin(filter, {"Рейтинг магазина", "артикул"}, expandcalcRating, {"Рейтинг магазина", "артикул"}, "calculateRating", JoinKind.LeftOuter),
    expand2 = Table.ExpandTableColumn(join2, "calculateRating", {"Можно распределить"}, {"Можно распределить"}),
    addCol = Table.AddColumn(expand2, "Процент распределения", each [Можно распределить]/[Потребность], Percentage.Type),
    join3 = Table.NestedJoin(expand, {"Рейтинг магазина", "артикул"}, addCol, {"Рейтинг магазина", "артикул"}, "addCol", JoinKind.LeftOuter),
    expand3 = Table.ExpandTableColumn(join3, "addCol", {"Процент распределения"}, {"Процент распределения"}),
    replaceNull = Table.ReplaceValue(expand3,null,0,Replacer.ReplaceValue,{"Процент распределения"}),
    addCol2 = Table.AddColumn(replaceNull, "Обеспечиваемая потребность", each Number.RoundUp([Потребность]*[Процент распределения]), Int64.Type),
    calcResult = Table.Group(addCol2, {"артикул"}, {{"tmp", РаспределениеПоМагизинам, type table [Рейтинг магазина=Int64.Type, Потребность=Int64.Type, Доступность=Int64.Type, Распределяем=Int64.Type]}}),
    expandcalcResult = Table.ExpandTableColumn(calcResult[[tmp]],"tmp",{"Магазин", "Рейтинг магазина", "артикул", "Потребность", "Доступность", "Распределяем"}), 
    transform2 = Table.TransformColumns(expandcalcResult, {"Потребность", (Потребность)=>-Потребность, Int64.Type})

in
    transform2
Функция РаспределениеПоПриоритетам:
Код
(subTable)=> 
    let
        setTable = Table.Sort(subTable, {{"Рейтинг магазина", Order.Ascending}}),
        items = List.Buffer(Table.ToRecords(setTable)),
        itemCount = List.Count(items),
        generate = List.Generate(
            () => [i = 0, rec = items{i}, Остаток = List.Max({0, rec[Доступность]-rec[Потребность]}), Можно распределить=List.Min({rec[Доступность], rec[Потребность]})],
            each [i] < itemCount,
            each [
                i = [i] + 1, 
                rec = items{i}, 
                Можно распределить = List.Min({[Остаток],rec[Потребность]}),
                Остаток = List.Max({0, [Остаток]-rec[Потребность]})

            ],
            each [[rec], [Можно распределить]]
        ),
        toTable = Table.FromRecords(generate),
        expandRecords = Table.ExpandRecordColumn(toTable, "rec", Table.ColumnNames(subTable))
    in
        expandRecords

Функция РаспределениеПоМагазинам:

Код
(subTable)=> 
    let
        setTable = Table.Sort(subTable,{{"Рейтинг магазина", Order.Ascending}, {"Потребность", Order.Descending}}),
        items = List.Buffer(Table.ToRecords(setTable)),
        itemCount = List.Count(items),
        generate = List.Generate(
            () => [i = 0, rec = items{i}, Остаток = List.Max({0, rec[Доступность]-rec[Обеспечиваемая потребность]}), Распределяем=List.Min({rec[Доступность], rec[Обеспечиваемая потребность]})],
            each [i] < itemCount,
            each [
                i = [i] + 1, 
                rec = items{i}, 
                Распределяем = List.Min({[Остаток],rec[Обеспечиваемая потребность]}),
                Остаток = List.Max({0, [Остаток]-rec[Обеспечиваемая потребность]})

            ],
            each [[rec], [Распределяем]]
        ),
        toTable = Table.FromRecords(generate),
        expandRecords = Table.ExpandRecordColumn(toTable, "rec", Table.ColumnNames(subTable))
    in
        expandRecords
 
 
 
#3
27.07.2021 18:33:41
Спасибо большое! Но я видимо неправильно сформулировала. у меня все данные в одной таблице, Related не подходит. Как сделать так, чтоб считалось из одной таблицы
Изменено: Алена - 27.07.2021 18:35:15
 
 
 
#4
27.07.2021 18:54:38
Алена, вычисляемые столбцы в PP

Код
Распределено =
VAR CurPriority = 'Потребность'[Рейтинг магазина] 
VAR CurCode = 'Потребность'[артикул] 
VAR CurNeed = - 'Потребность'[Потребность] 
VAR Available = 'Потребность'[в доступности к распределению] 
VAR MorePriority = FILTER ( 'Потребность'; 'Потребность'[артикул] = CurCode && 'Потребность'[Рейтинг магазина] < CurPriority ) 
VAR ResMorePriority = MIN ( SUMX ( MorePriority; - 'Потребность'[Потребность] ); Available ) 
VAR CurRes = MIN ( CurNeed;  Available - ResMorePriority ) 
 
RETURN CurRes

Код
Остаток после распределения = 
VAR CurPriority = 'Потребность'[Рейтинг магазина] 
VAR CurCode = 'Потребность'[артикул] 
VAR Available = 'Потребность'[в доступности к распределению] 
VAR MorePriority = FILTER ( 'Потребность'; 'Потребность'[артикул] = CurCode && 'Потребность'[Рейтинг магазина] <= CurPriority ) 
VAR ResMorePriority = MIN ( SUMX ( MorePriority; - 'Потребность'[Потребность] ); Available ) 
 
RETURN Available - ResMorePriority

 
 
 
#5
28.07.2021 09:18:26
Спасибо Вам огромное, но вчера крутила файл и понимаю, что не хватает еще одного параметра. Надо кроме рейтинга магазина добавлять и его АВС. Тут своими силами пыталась изменить что-то и не получается (( Посмотрите плз файл в приложении ...
 
 
 
#6
28.07.2021 14:32:02
Алена,
Код
Распределено =
VAR CurPriority = 'Потребность'[ABC магазина] & 'Потребность'[Рейтинг магазина] 
VAR CurCode = 'Потребность'[артикул] 
VAR CurNeed = - 'Потребность'[Потребность] 
VAR Available = 'Потребность'[в доступности к распределению] 
VAR MorePriority = 
   FILTER ( 
      'Потребность'; 
      'Потребность'[артикул] = CurCode 
         && ( 'Потребность'[ABC магазина] & 'Потребность'[Рейтинг магазина] ) < CurPriority 
   ) 
VAR ResMorePriority = MIN ( SUMX ( MorePriority; - 'Потребность'[Потребность] ); Available ) 
VAR CurRes = MIN ( CurNeed;  Available - ResMorePriority ) 
  
RETURN CurRes
Код
Остаток после распределения = 
VAR CurPriority = 'Потребность'[ABC магазина] & 'Потребность'[Рейтинг магазина] 
VAR CurCode = 'Потребность'[артикул] 
VAR Available = 'Потребность'[в доступности к распределению] 
VAR CurAndMorePriority = 
   FILTER ( 
      'Потребность'; 
      'Потребность'[артикул] = CurCode 
         && ( 'Потребность'[ABC магазина] & 'Потребность'[Рейтинг магазина] ) <= CurPriority 
   ) 
VAR ResCurAndMorePriority = MIN ( SUMX ( CurAndMorePriority; - 'Потребность'[Потребность] ); Available ) 
  
RETURN Available - ResCurAndMorePriority
 
 
 
#7
29.07.2021 13:21:41
surkenny, спасибо огромное, все работает!!
 
 
 
Страницы: 1
Наверх