Суммируйте значения в фильтре отдельных строк

Мне нужно рассчитать общую стоимость доставки для некоторых заказов на продажу. Набор данных выглядит следующим образом:

Проблема, с которой я столкнулся, заключается в том, что, хотя стоимость доставки должна учитываться только один раз для каждого заказа в расчете, в наборе данных она повторяется для каждой позиции заказа, поэтому простые дубликаты затрат на доставку:

=SUM(MyTable[Shipping]) = 90 // wrong value

Однако мне нужно:

  • фильтровать таблицу, чтобы сохранить только 1 строку для каждого заказа
  • подвести итог доставки

Что должно быть что-то вроде:

=SUMX(FILTER(MyTable,<filter>),MyTable[Shipping]) = 35 // correct value

Но я изо всех сил пытаюсь написать <filter>, я нашел DISTINCT который возвращает список уникальных идентификаторов заказа, но не соответствующую им строку.

Кто-нибудь есть идеи, как я мог бы написать фильтр для правильного расчета доставки?

Источник

3 ответа

Решение

Функции X не интуитивны, но очень мощны - вы находитесь на правильных линиях.

Я хотел бы подойти к этому с двумя мерами, первый для суммирования стоимости доставки и деления ее на количество строк для этого заказа. (Ключом ко второй половине является ALL(), который открывает контекст столбца, на который ссылаются, в то же время сохраняя другие контексты.)

И второе, чтобы повторить эту меру по порядку и суммировать результаты.

[Allocated Shipping] =
                      SUM ( MyTable[Shipping] )
                    / CALCULATE ( COUNTROWS ( MyTable ), ALL ( MyTable[Item] ) )

[Iterated Shipping] = 
                      SUMX(VALUES(MyTable[Order]), [Allocated Shipping])
Источник

Очень похоже на рекомендацию Гэри, вы можете использовать (в дополнительном столбце - E2):

=IF(COUNTIF($A$2:A2,A2)>1,D2,0)

Это покажет стоимость доставки в самом цв. Затем вы можете просто СУММА (E2:E), чтобы увидеть общую стоимость (35).

Источник

Самый простой подход - использовать столбец Helper. В E2 введите:

=IF(COUNTIF($A$1:A2,A2)>1,0,1)

и скопировать вниз. Это идентифицирует уникальные значения в столбце А. Для суммирования этих уникальных значений используйте:

=SUMPRODUCT(--(E2:E9=1)*(D2:D9))

Для ваших данных:

пример

Значение 35

Естественно, если бы данные были отфильтрованы, вы бы использовали вариант функции SUBTOTAL() или дополнительный вспомогательный столбец.

Источник
Другие вопросы по тегам