Вопрос или проблема
У меня есть сценарий, в котором я создал сводную таблицу на основе исходных данных. Сводная таблица содержит:
- Имя лида,
- Количество ролей
- и общее количество
Пример сводной таблицы:
| Лид | Количество1 | Общее количество |
|---|---|---|
| Джон | 23 | 40 |
| Уилл | 15 | 35 |
| Райан | 20 | 30 |
У меня есть фильтр под названием ‘Роль’, который имеет значения такие как Разработчик/QA/Администратор и т.д.
Моя потребность заключается в том, чтобы, независимо от выбранной роли в фильтре, Общее количество не менялось (значения оставались неизменными/нефильтрованными). Только Количество1 должно фильтроваться. Есть идеи?
Любой фильтр, примененный к сводной таблице, применяется ко всем данным в сводной таблице.
Вы можете использовать более сложные вычисления с помощью Power Pivot и DAX, если готовы потратить время на изучение DAX, это будет наиболее гибкий способ. Однако следует добавить, что DAX не является интуитивно понятным для начинающих, поэтому, если у вас нет потребности регулярно изучать и использовать DAX, возможно, это не стоит того.
Другой способ — добавить столбец в ваши исходные данные, который предварительно вычисляет количество строк для каждого лида.
Предположим, в ваших исходных данных два столбца:
- Лид в столбце A
- Роль в столбце B
Предположим, у вас есть 1 строка заголовков в строке 1 и 105 строк данных, представляющих 40+35+30 строк для каждого лида.
Затем добавьте третий столбец ‘Общее количество’ и используйте эту формулу во второй строке, затем протяните её вниз до оставшихся строк:
=COUNTIF($A$2:$A$106,$A2)
Вы можете затем использовать либо Min, Max, либо Среднее значение Общего количества в вашей сводной таблице, и оно всегда будет одинаковым для каждого лида, независимо от примененных фильтров.
Ответ или решение
В современном мире обработки и анализа данных сводные таблицы предоставляют мощные возможности для визуализации и обобщения информации. Однако иногда стандартные функции сводных таблиц могут не удовлетворять специфическим требованиям бизнеса, как в случае, описанном вами. Вы хотите, чтобы при изменении фильтра «Роль» в вашей сводной таблице изменялись только значения в столбце «Count1», в то время как показатели в столбце «All Count» оставались неизменными. Это нетривиальная задача, и мы обсудим, как ее можно решить.
Теоретическая часть
Во-первых, важно понять, как работает сводная таблица. При применении фильтров к сводной таблице происходит перерасчет всех связанных данных. Это означает, что сегментация или фильтрация одной переменной обычно влияет на весь набор данных, включая все вычисляемые показатели.
Для решения проблемы изолирования одного метрика от воздействия фильтров есть несколько путей, каждый из которых имеет свои особенности и требования:
-
DAX и Power Pivot: Может быть использован в Microsoft Excel и Power BI для написания комплексных формул. DAX (Data Analysis Expressions) предоставляет богатый набор функций для математических и логических вычислений, которые можно использовать для более тонкого анализа данных.
-
Предварительная обработка данных: Один из способов заключается в добавлении дополнительного столбца в исходные данные, который бы содержал необходимый фиксированный расчет заранее.
-
Условные функции Excel: Использование функций Excel, таких как COUNTIF или SUMIF, для вычисления необходимых величин прямо в исходной таблице данных.
Пример
Рассмотрим пример, как можно применить один из предложенных подходов. Допустим, у вас есть исходная таблица, в которой столбец «Lead» находится в A, а столбец «Role» в B. Для предварительного подсчета общего количества ролей для каждого руководителя (Lead), можно добавить новый столбец «All Count» с использованием формулы COUNTIF:
=COUNTIF($A$2:$A$106, $A2)Эта формула вычисляет общее количество строк для конкретного руководителя от начала до конца данных, которые вводятся начиная со второй строки (на основе вашего примера). Данная формула может быть протянута вниз, заполнив соответствующее значение для каждой записи.
Применение на практике
Если вы предпочитаете использовать Excel или Power BI с Power Pivot и DAX, можно создать вычисляемую меру в модели данных, которая зафиксирует значение общего количества для каждого руководителя, невзирая на примененные фильтры. Рассмотрим, как это может быть сделано:
-
Создание меры в Power Pivot:
- Перейдите в модель данных в Power Pivot.
- Создайте новую меру, использовав следующую формулу:
AllCount = CALCULATE(COUNTROWS(YourTable), ALLEXCEPT(YourTable, YourTable[Lead])) - Функция ALLEXCEPT позволяет игнорировать фильтры всех столбцов, кроме указанного, в данном случае столбца Lead.
-
Использование созданной меры в отчетах:
- После создания меры AllCount, добавьте ее в ваш отчет или визуализацию.
- Применяемые фильтры на поле Role больше не будут влиять на результаты в столбце «All Count».
Этот подход позволяет разграничить влияние фильтров на отдельные метрики и добиться нужного вам уровня детализации и точности.
Заключение
Сводные таблицы, Power Pivot и DAX являются мощными инструментами, предоставляющими множество возможностей для бизнес-анализа и адаптации данных под конкретные нужды. Каждый из предложенных вариантов решения имеет свои преимущества и требует определённых навыков и знаний для реализации. Выбор подхода должен основываться на вашем опыте и необходимости учёта специфических бизнес-логик. Если у вас нет опыта работы с DAX, а задача требует простого решения, предварительная обработка данных и использование функций Excel могут быть наиболее подходящим выбором для вас. Однако, если ваша работа регулярно требует гибких и сложных данных, изучение DAX может быть полезной инвестицией в ваш профессиональный рост.