Использование промежуточного итога группы столбцов в формуле вычисляемого поля
Я собираю данные в таблицу Excel с помощью Power Query, а затем представляю их в сводной таблице.
В сводной таблице есть одно поле, включенное в область «Столбцы», в результате чего отдельные значения в этом поле переносятся в заголовок в виде столбцов, что позволяет мне суммировать другое поле, но сгруппировано по каждому из этих значений, все как обычно и ожидаемо.
Но теперь я также сгруппировал эти отдельные значения в две группы и отобразил промежуточные итоги столбца для каждой группы, что также отлично работает.
Но, наконец, я хочу добавить в таблицу дополнительный столбец, показывающий общий итог как долю одного из двух промежуточных итогов столбца, и я просто не могу найти способ сделать это.
Я пробовал искать встроенные функции сводной таблицы, но ничего не нашел. Вместо этого я также попытался добавить таблицу исходных данных в модель данных и использовать Power Pivot для создания меры для достижения этой цели, но опять же безуспешно.
Чтобы проиллюстрировать это, приведем минимальный пример с некоторыми фиктивными данными — сначала некоторые исходные данные в таблице:
Обновлено: В соответствии с просьбой, вот фиктивные исходные данные в текстовой форме:
Полный файл .xlsx, который я использовал для этого примера, также доступен для скачивания здесь.
И как выглядит моя сводная таблица:
Итак, моя настройка сводной таблицы выглядит следующим образом:
Поле «Счет №2» в области «Столбцы» появилось только после того, как я сгруппировал столбцы по «Доходу» и «Себестоимости продаж».
Итак, чего я хочу достичь, так это процента валовой прибыли, который будет отображаться в каждой строке, независимо от того, что расширяется/сжимается, вот так:
или вот так, если я свернусь до Customer:
По сути, валовая прибыль в % для проекта P001 составляет 1000/2250, а для P002 — 1860/4625 (и аналогично для цифр, показанных при расширении отдела, показывающих валовую прибыль на отдел для этого проекта, а не для всего проекта в целом), поэтому я пытаюсь найти способ показать вычисляемое поле или подобное, которое может выполнить этот расчет, и включить его в мою сводную таблицу.
Надеюсь, кто-то уже сталкивался с подобной проблемой и может предложить несколько советов! Файл примера, который я использовал для создания этих снимков экрана, можно скачать здесь, если будет полезно поиграться с моими фиктивными данными и сводной таблицей.
Спасибо, Рон, я сделаю это сейчас, хотя полный файл .xlsx также прилагается, если люди захотят его загрузить и попытаться мне помочь.
Рассматривали ли вы возможность использования диапазонов разливов вместо сводных таблиц? Может быть, лучше для того, что вы делаете, или использование сводной таблицы является абсолютным требованием? Вы могли бы создать диапазоны разлива с вашими уникальными строками/столбцами, а затем использовать countifs/averageifs/sumifs и т. д. для агрегирования данных, а не сводной таблицы?
@AndyRobertson - нет, я раньше не сталкивался с диапазонами разливов - я погуглю и посмотрю, спасибо.
Ответы 2
У вас должна быть возможность добавлять столбцы в запрос мощности. После импорта данных добавьте настраиваемый столбец с нужными вычислениями. Как только у вас появится новый столбец, он будет логически связан с конкретным номером документа и поэтому должен отображаться в отчете сводной таблицы.
Проблема в том, что у меня есть строки, транспонированные в столбцы сводной таблицы, и именно значения в этих столбцах я хочу выполнить вычисления, поэтому эти значения недоступны мне на этапе получения данных Power Query. Взгляните на фиктивный пример, который я привел в вопросе, и если вы сможете реализовать что-то, что работает, дайте мне знать!
@3N1GM4 какие строки/столбцы переставлены?
все столбцы «Нет учетной записи» в соответствии с примером, прикрепленным к моему вопросу, и подробностями, указанными в вопросе.
Добавьте два столбца в таблицу исходных данных. Составив формулы следующим образом:
Счет-фактура Вал =IF([@Amount]<0,ABS([@Amount]),"")
ПО Вал =ЕСЛИ([@Сумма]>0,[@Сумма],"")
В сводную таблицу добавьте вычисляемые поля. =ABS(Сумма)/ЗначениеСчета Это должно дать вам процент от абсолютной суммы, которую затем нужно отформатировать в процентах.
На самом деле вам не нужен столбец PO Val в исходных данных! Извиняюсь за это.
Спасибо, Энди, я попробую сегодня, приму твой ответ и назначу награду, если это сработает для меня.
Это работает, и это здорово. Можете ли вы просто помочь мне с тем, как я могу скрыть все дополнительные столбцы, которые добавляются в сводную таблицу при добавлении этого вычисляемого поля? Я просто хочу увидеть окончательную маржу для каждой строки («Общая сумма теста» в вашем примере), а не все отдельные столбцы «Сумма теста», которые также появляются...
Я понимаю, что мог бы просто скрыть их в Excel обычным способом, но набор возможных транспонированных столбцов несколько динамичен, поэтому я не думаю, что это сработает.
Извините, некоторое время отсутствовал. Спасибо, что приняли ответ, удалось ли вам скрыть то, чего вы не хотели? Я думаю, вам, вероятно, просто нужно удалить промежуточные итоги в различных полях, но я еще не проверял это. Быстро осмотрю и вернусь.
Привет, Энди, я только что пошел по пути скрытия традиционным способом в Excel, но если вы можете предложить более элегантное решение, я был бы очень признателен. Спасибо!
Чтобы данные были полезными, отредактируйте свой вопрос и опубликуйте его в виде текста, возможно, используя этот Генератор таблиц разметки.