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

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

введите описание изображения здесь

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

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

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

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

Это должно быть примерно так:

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

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

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


excel business-intelligence powerpivot dax
person Max    schedule 03.12.2014    source источник

Ответы (3)


arrow_upward
2
arrow_downward

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

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

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

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

[Iterated Shipping] = 
                      SUMX(VALUES(MyTable[Order]), [Allocated Shipping])
person Jacob    schedule 03.12.2014

arrow_upward
1
arrow_downward

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

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

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

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

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

пример

Значение 35.

Естественно, если данные были отфильтрованы, вы должны использовать вариант функции ПРОМЕЖУТОЧНЫЙ ИТОГ () или дополнительный вспомогательный столбец.

person Gary's Student    schedule 03.12.2014
comment
Хорошая идея, но это не сработает, если 2 последовательных заказа имеют одинаковую стоимость доставки (что случается довольно часто, поскольку наши расходы на доставку являются стандартными), поскольку нет изменений в значениях, которые вы можете определить с помощью своего вспомогательного столбца. - person Max; 03.12.2014
comment
Я не понимаю .... если два последовательных заказа имеют разные номера заказов, они будут забраны обоими. - person Gary's Student; 03.12.2014
comment
В самом деле, извините, я думал, что вы использовали стоимость доставки для обнаружения изменений, это действительно сработает. - person Max; 04.12.2014

arrow_upward
1
arrow_downward

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

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

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

person Melski    schedule 03.12.2014