Страницы: 1
RSS
Сумма произведений с условием в POWER PIVOT
 

Здравствуйте, уважаемые эксперты Excel.

Допустим создана таблица из 2 столбцов, в первом столбце под названием «№» перечислены номера по порядку от 1 до 50, во втором столбце числовые значения. Как с помощью создания меры в power pivot можно найти сумму произведения столбца «Значение», с созданием двух разных условий. К примеру, фильтруем таблицу по условию, где номера в столбце «№» меньше 26 и умножаем столбец «Значения» на таблицу, где номера в столбце «№» (той же таблицы) больше >= 26. Получается, что перемножаем и далее суммируем один и тот же столбец, но с разными фильтрами. Понимаю, что можно применить к примеру SUMX, FILTER, CALCULATE и прочие функции, но в результате у меня ничего не выходит.

Я так думаю, можно было бы написать следующую меру: СУММПРОИЗВ:=SUMX('Таблица1';'Таблица1'[Значение]*'Таблица1'[Значение]), но не могу понять как тут отобразить два разных фильтра. Если бы был один фильтр, то можно было бы это сделать с помощью CALCULATE, но тут два разных фильтра для одной таблицы.

 
Если я правильно понял задачу, то нужно сумму из столбца "Значение" идущую до № 25 умножить на сумму значений столбца "Значения" под номерами от 26 и выше.

Если так, то как вариант:
Код
СУММПРОИЗВ :=
VAR minus =
    SUMX ( FILTER ( 'Таблица1'; 'Таблица1'[№] < 26 ); 'Таблица1'[Значение] )
VAR plus =
    SUMX ( FILTER ( 'Таблица1'; 'Таблица1'[№] >= 26 ); 'Таблица1'[Значение] )
RETURN
    minus * plus


Близко к варианту, который Вы изначально обдумывали, без переменных, так:
Код
sumx(Filter('Таблица1';'Таблица1'[№]<26);'Таблица1'[Значение])*sumx(Filter('Таблица1';'Таблица1'[№]>=26);'Таблица1'[Значение])


То есть, сначала Вы фильтруете таблицу как надо, а потом уже по отфильтрованной таблице считаете результат.
Изменено: Dyroff - 22.01.2021 23:23:05
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Dyroff,большое спасибо, с переменными наглядней и удобнее. Но ваше решение не совсем мне подходит, так как исходя из вашего кода получается, что сначала суммируем столбец, а потом умножаем. А нужно сначала умножить каждую строчку столбца, а потом суммировать результат умножения.

Я думаю наверное для начала, нужно создать две переменные, где каждая переменная отдельно отфильтрованная таблица. Потом склеить столбцы из переменных в единую таблицу. Сейчас создал две переменные, где в каждой отдельно создал свой фильтр, но не могу понять как эти отфильтрованные столбцы теперь из разных виртуальных таблиц объединить в одну таблицу. Все варианты которые я сейчас нашел, они склеивают строки из разных таблиц. А нужно создать таблицу с этими двумя столбцами. Возможно я сейчас рассуждаю в неправильном направлении и решение должно быть кардинально другое.
 
Покажите, как должен выглядеть результат
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
Dyroff, вручную отфильтровал значения, пример прикрепил.

А я сейчас пробую виртуально созданные (отфильтрованные) таблицы соединить с помощью функции CROSSJOIN, но пока что ничего не выходит)))
 
daniil.k, Так моя мера дает такой же результат. Итоговое число 170890140
Изменено: Dyroff - 23.01.2021 00:06:41
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 

В итоге считает неправильно, получается число 170890140, которое получается если сначала суммировать каждый столбец и итоговые значения столбцов умножить друг на друга. А должно получиться число равное 10513503, которое получается, если мы сначала каждую строку умножаем, а потом перемноженные столбцы суммируем. В итоге почему неправильно считает? Может нужно исправить код или может есть другое решение? Файл с пример прикрепил. Для наглядности добавил стрелочки, чтобы было понятно как считаются значения.

Код
СУММПРОИЗВ:=VAR Table_X = SELECTCOLUMNS (FILTER ( 'Таблица1'; 'Таблица1'[№] < 26 );"Столбец_X";'Таблица1'[Значение] )VAR Table_Y = SELECTCOLUMNS (FILTER( 'Таблица1'; 'Таблица1'[№] >= 26 );"Столбец_Y";'Таблица1'[Значение] )VAR Table_XY = CROSSJOIN(Table_X;Table_Y)RETURNSUMX (Table_XY;[Столбец_X]*[Столбец_Y])

 
Доброе время суток
Цитата
daniil.k написал:
должно получиться число равное 10513503
Так оно так и получается.
Код
=
VAR less26 =
    SELECTCOLUMNS (
        CALCULATETABLE ( 'Таблица1'; 'Таблица1'[№] < 26 );
        "cless26"; 'Таблица1'[№]
    )
VAR moreOE26 =
    SELECTCOLUMNS (
        CALCULATETABLE ( 'Таблица1'; 'Таблица1'[Значение] >= 26 );
        "cmoreOE26"; 'Таблица1'[Значение]
    )
VAR cross =
    CROSSJOIN ( less26; moreOE26 )
VAR result =
    SUMX ( cross; [cless26] * [cmoreOE26] )
RETURN
    result
Изменено: Андрей VG - 23.01.2021 10:00:33
 
Андрей VG, не понимаю почему именно так считает  :(  Я сейчас, вручную отфильтровал таблицу, назвал ее "Отфильтрованная_таблица", добавил в модель данных, создал новую меру и в итоге получилось нужное мне значение равное 10513503. Но почему вручную получается нужный результат, а вот созданные виртуально таблицы дают совершенно другой результат. Видимо я что-то делаю принципиально не правильно.
Код
ОТФ_СУММПРОИЗВ:=SUMX('Отфильтрованная_таблица';'Отфильтрованная_таблица'[Значение_X]*'Отфильтрованная_таблица'[Значение_Y])
Изменено: daniil.k - 23.01.2021 11:10:40
 
Упс, дошло
Исправлено, вариант
Код
=
VAR less26 =
    CALCULATETABLE ( 'Таблица1'; 'Таблица1'[№] < 26 )
VAR toKeyLess =
    SELECTCOLUMNS ( less26; "№"; 'Таблица1'[№] - 0; "X"; 'Таблица1'[Значение] )
VAR moreOE26 =
    CALCULATETABLE ( 'Таблица1'; 'Таблица1'[№] >= 26 )
VAR toKeyMore =
    SELECTCOLUMNS ( moreOE26; "№"; 'Таблица1'[№] - 25; "Y"; 'Таблица1'[Значение] )
VAR joinLessMore =
    NATURALINNERJOIN ( toKeyLess; toKeyMore )
RETURN
    SUMX ( joinLessMore; [X] * [Y] )
Изменено: Андрей VG - 23.01.2021 11:40:59
 
Андрей VG,Ураааа, работает, большое вам спасибо.
Страницы: 1
Наверх