Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Создание "промежуточной" сводной таблицы в Power Pivot, получить промежуточные сводные данные для последующей фильтрации и агрегирования
 
#1
15.05.2024 17:05:36
Добрый день,

Прошу помочь советом в следующей ситуации:
1. Есть некий набор данных - идентификатор заявки (StageId), дата создания заявки (Дата заявки) и время выполнения заявки.
2. Одна заявка может выполняться несколько раз

Необходимо получить некий промежуточный результат - таблица вида: StageId, Суммарное время выполнения с тем, чтобы в результирующую таблицу отобрать только заявки, общая длительность которых, например, больше 60 минут.

Пробовал сделать промежуточную таблицу в Power Pivot через Summarize:
Код
мера:=SUMMARIZE('Таблица1';'Таблица1'[StageId];"tmp";sum('Таблица1'[Итого, мин.]))
, но неизменно получаю, что "выражение ссылается на несколько столбцов".

Подскажите, пожалуйста, что не так делаю.
Заранее благодарю за конструктивные ответы.
 
 
 
#2
15.05.2024 18:16:44
В PP нельзя создавать вычисляемые таблицы, кроме календаря.
Такой функционал есть только в PBI.
Вы пишете выражение для меры! Мера может возвращать только скалярное значение (какое-то одно значение: цифру, дату, текст…), но не список/таблицу и тп.
Вы можете с легкостью создать такую таблицу в PQ.
Другой вопрос, нужно ли Вам это :)
Чуть позднее приложу вариант.

upd:
Может быть, Вам будет достаточно такого варианта:
- создаем вычисляемый столбец
Код
Общая длительность более 60 мин =
=
CALCULATE (
    SUM ( 'Таблица1'[Итого, мин.] );
    ALLEXCEPT ( 'Таблица1'; 'Таблица1'[StageId] )
) > 60

- просто вытаскиваем этот столбец в фильтр сводной и выбираем ИСТИНУ. Ну или в срез такой столбец вытащить.
Изменено: surkenny - 15.05.2024 18:29:58
 
 
 
#3
15.05.2024 19:13:59
Цитата
Sviman144 написал:
Необходимо получить некий промежуточный результат - таблица вида: StageId, Суммарное время выполнения с тем, чтобы в результирующую таблицу отобрать только заявки, общая длительность которых, например, больше 60 минут.
Почему именно так?
А просто
Код
=IF(SUM('Таблица1 1'[Итого, мин.])>60;SUM('Таблица1 1'[Итого, мин.]);BLANK())
НА МЯСО В ПРОФИЛЕ
 
 
 
#4
15.05.2024 19:47:13
Surkenny, спасибо за ответ, но, по-моему, это немного не то. Не хотелось бы в итоговой сводной таблице использовать дополнительные столбцы или срезы.
Хотелось бы получить нужную "сумму" непосредственно уже в Power Pivot. Такое возможно?

"Исходная" таблица создается изначально в PQ как результат запроса к БД.

По идее, формула должна быть что-то вроде:
Код
мера := CALCULATE(
SUM ( 'Таблица1'[Итого, мин.] );
SUM ( 'Таблица1'[Итого, мин.] ) > 60
)
, но такая - не работает. :)

Пробовал добавить доп. столбец в PQ с формулой вроде:
Код
Table.AddColumn(Источник, "tmp", each List.Sum(Table.SelectRows(Источник (x) => x[StageId]  = _[StageId])[#"Итого, мин."]))
,но оооочень долго считает результат. Может быть есть более быстрый вариант, как "выделить" только те заявки, у которых сумма выполнения больше определенного времени, в том же PQ, если в PP это нереально?
Изменено: Sviman144 - 15.05.2024 19:49:57
 
 
 
#5
15.05.2024 20:08:16
Цитата
Михаил Л написал:
IF(SUM('Таблица1 1'[Итого, мин.])>60;SUM('Таблица1 1'[Итого, мин.]);BLANK())
Спасибо, так - похоже, но:
1. Общий итог в новом столбце отражает полному количеству минут (с учетом тех заявок, которые не удовлетворяют критерию).
2. Если добавить еще один столбец, группирующий заявки (например, по отделу), то в конечной сводной сумма считается также общая, без учета критерия
 
 
 
#6
15.05.2024 20:36:55
Цитата
Sviman144 написал:
в конечной сводной сумма считается также общая, без учета критерия
А так?
Код
=
var a = SUMMARIZE('Таблица1';'Таблица1'[StageId];"tmp";IF(SUM('Таблица1'[Итого, мин.])>60;SUM('Таблица1'[Итого, мин.]);BLANK()) )

return SUMX(a;[tmp])
НА МЯСО В ПРОФИЛЕ
 
 
 
#7
15.05.2024 20:40:34
Sviman144,
1. Есть более быстрый вариант: группировка по id, в агрегации 2 столбца: сумма по минутам и просто вся таблица. Затем фильтруете по сумме > 60 и разворачиваете из столбца с таблицей дату и сумму.
2. Не у компа.
Но такую меру попробуйте:
Код
Сумма мин :=
VAR sumByIDs =
    ADDCOLUMNS (
        VALUES ( 'Таблица1'[StageId] );
        "@sum"; CALCULATE ( SUM ( 'Таблица1'[Итого, мин.] ) )
    )
VAR filterIDs =
    FILTER ( visibleIDs; [@sum] > 60 )
VAR result =
    SUMX ( filterIDs; [@sum] )
RETURN
    result

3. Производительнее будет все-таки с допстолбцом. Он вычислится один раз. А не каждый раз динамически в мере пересчитывать.
Код
moreThen60 =
=
CALCULATE (
    SUM ( 'Таблица1'[Итого, мин.] );
    ALLEXCEPT ( 'Таблица1'; 'Таблица1'[StageId] )
) > 60

И мера
Код
Сумма мин:=
CALCULATE ( SUM ( 'Таблица1'[Итого, мин.] ); 'Таблица1'[moreThen60] )
Изменено: surkenny - 15.05.2024 21:23:51
 
 
 
#8
15.05.2024 20:47:24
del
Изменено: surkenny - 15.05.2024 21:24:17
 
 
 
#9
16.05.2024 11:25:00
Цитата
написал:
А так?Код=
var a = SUMMARIZE('Таблица1';'Таблица1'[StageId];"tmp";IF(SUM('Таблица1'[Итого, мин.])>60;SUM('Таблица1'[Итого, мин.]);BLANK()) )

return SUMX(a;[tmp])
Круто! Не знал, что "так" можно с "мерами" :)
Спасибо!
 
 
 
#10
16.05.2024 11:56:43
Благодарю за ответ, surkenny.

1. Буду рад дождаться Вас "у компа", чтобы получить вариант с решением задачи через PQ, т.к. очень интересует также и такой вариант.
Цитата
surkenny написал:
Sviman144 ,
1. Есть более быстрый вариант: группировка по id, в агрегации 2 столбца: сумма по минутам и просто вся таблица. Затем фильтруете по сумме > 60 и разворачиваете из столбца с таблицей дату и сумму.
2. Не у компа.
2. А можно, в принципе, получить еще один столбец в таблице PP, где бы для каждой строки рассчитывалась общая сумма выполнения заявки? В строках, где Id заявок дублируется, также, получается, дублировалась сумма. (получается, альтернатива столбцу в PQ)

3. Спасибо. Так - "работает"! Но слышал, что добавление столбцов вместо мер менее правильный вариант, т.к. производительность "мер" гораздо выше нежели "доп. столбцов".
Изменено: Sviman144 - 16.05.2024 11:57:21
 
 
 
#11
16.05.2024 15:46:06
1.
Цитата
Sviman144 написал:
слышал, что добавление столбцов вместо мер менее правильный вариант, т.к. производительность "мер" гораздо выше нежели "доп. столбцов"
Вы где-то слышали, даже не приводите источник, но игнорируете написанное мной?
Цитата
surkenny написал:
Производительнее будет все-таки с допстолбцом. Он вычислится один раз. А не каждый раз динамически в мере пересчитывать.
2.
Цитата
Sviman144 написал:
А можно, в принципе, получить еще один столбец в таблице PP, где бы для каждой строки рассчитывалась общая сумма выполнения заявки?
Еще раз, Вы мои ответы читали? Пробовали применить?
Как думаете, что нужно убрать в выражении, чтобы получить сумму (а не больше/не больше эта сумма 60)?
Код
moreThen60 =
CALCULATE (
    SUM ( 'Таблица1'[Итого, мин.] );
    ALLEXCEPT ( 'Таблица1'; 'Таблица1'[StageId] )
) > 60

Мне кажется, очевидно, что следующий столбец - именно то, что Вам нужно. Найдите 3 отличия (кроме наименования столбца и пробелов).
Код
sumById =
CALCULATE (
    SUM ( 'Таблица1'[Итого, мин.] );
    ALLEXCEPT ( 'Таблица1'; 'Таблица1'[StageId] )
)

3. Я же даже расписал, что нужно сделать в PQ. В чем проблема?
Цитата
surkenny написал:
Есть более быстрый вариант: группировка по id, в агрегации 2 столбца: сумма по минутам и просто вся таблица. Затем фильтруете по сумме > 60 и разворачиваете из столбца с таблицей дату и сумму.
Если попробуете сделать, но у Вас не получится, можете создать отдельную тему с вопросом.
Изменено: surkenny - 16.05.2024 15:47:22
 
 
 
#12
17.05.2024 09:28:29
Благодарю за ответ.
Тему можно закрывать.
 
 
 
Страницы: 1
Читают тему
Наверх