
Сломанная иерархия
Рвав-рвав, здесь и сейчас с вами собака Смайл!
Для начала немного о том, почему я грустный:
Санкт-Петебрург, дождь, грязюка, холоднюка.
Приходится носить всякую фигню. Сопротивление бесполезно. Надеюсь, вы тоже мучаетесь, так как вместе - веселее.
Сегодня хотелось бы описать работу с так называемой “сломанной” иерархией данных. В принципе, это довольно часто встречающаяся ситуация, из которой каждый выходит так, как может, но собака Смайл в данной статье постарается разъяснить все доступным языком. Так сказать, “Повторение - мать учения.”
Пример:
В качестве исходных данных поступила информация следующего вида:
– основной набор данных, содержащий ID пользователя, номер документа и сумму - “Dataset”;
– справочник, содержащий информацию о структуре подчинения между сотрудниками - “Staff Hierarhy”.
Задача:
На основе представленных данных реализовать следующие требования:
– построить иерархию данных таким образом, чтобы у каждого сотрудника было одинаковое количество уровней иерархии;
– убрать повторяющиеся значения на разных уровнях иерархии, то есть, произвести ее “схлопывание”.
Построение структурированной иерархии в справочнике “Staff Hierarhy”:
В целях устранения хаоса в исходных данных для начала нам необходимо понять, является ли конкретное значение конечным элементом иерархии. В этом нам может помочь столбец “End Link”.
В принципе, делать его необязательно, но в случае большого объема данных он сможет помочь при проверке конечного результата:
Формула указанного столбца выглядит следующим образом:
End Link =
CALCULATE (
COUNTROWS ( 'Staff Hierarhy' ),
ALL ( 'Staff Hierarhy' ),
'Staff Hierarhy'[Parent Key] = EARLIER ( 'Staff Hierarhy'[User ID] )
) = 0
Следующим шагом является построение цепочки родительских значений, для последующего определения уровня иерархии. В этом нам поможет столбец “Hierarchy View”, рассчитанный при помощи функции “PATH”:
Формула указанного столбца рассчитывается так:
Hierarchy View =
PATH ( 'Staff Hierarhy'[User ID], 'Staff Hierarhy'[Parent Key] )
Подсчет уровня иерархии, проиллюстрированнный в столбце “Hierarchy Level”, можно произвести при помощи функции “PATHLENGHT”:
Формула указанного столбца выглядит следующим образом:
Hierarchy Level =
PATHLENGTH ( 'Staff Hierarhy'[Hierarchy View] )
Выполнив указанные шаги, можно приступать непосредственно к построению уровней иерархии при помощи функций “PATHITEM” и “LOOKUPVALUE”:
Формула столбца для 1-го уровня иерархии - “1st level” представляет собой следующую конструкцию:
1st level =
LOOKUPVALUE (
'Staff Hierarhy'[Name],
'Staff Hierarhy'[User ID], PATHITEM ( 'Staff Hierarhy'[Hierarchy View], 1, INTEGER )
)
Формула столбца для 2-го уровня иерархии - “2nd level” представляет собой следующую конструкцию:
2nd level =
IF (
'Staff Hierarhy'[Hierarchy Level] >= 2,
LOOKUPVALUE (
'Staff Hierarhy'[Name],
'Staff Hierarhy'[User ID], PATHITEM ( 'Staff Hierarhy'[Hierarchy View], 2, INTEGER )
),
'Staff Hierarhy'[1st level]
)
Формула столбца для 3-го уровня иерархии - “3rd level” представляет собой следующую конструкцию:
3rd level =
IF (
'Staff Hierarhy'[Hierarchy Level] >= 3,
LOOKUPVALUE (
'Staff Hierarhy'[Name],
'Staff Hierarhy'[User ID], PATHITEM ( 'Staff Hierarhy'[Hierarchy View], 3, INTEGER )
),
'Staff Hierarhy'[2nd level]
)
Собственно, 1-я цель по построению автоматическим путем выровненной иерархии на этом закончена, единственное, с чем надо определиться - это с необходимым количеством уровней, поскольку для каждого уровня требуется отдельный расчетный столбец.
Визуализация данных - стандартное поведение иерархии:
Используя стандартный элемент “Matrix” (“Матрица”), мы можем “соединить воедино” данные справочника “Staff Hierarhy” и основного набора данных - таблицы “Dataset”. Визуальный элемент будет выглядеть следующим образом:
При построении матрицы в иерархическом дереве присутствуют дубли в имени сотрудников. Это объяснимое поведение, поскольку “оно так работает”, однако повторяющиеся значения можно убрать, используя “дополнительные приседания”, а именно - соответствующие меры.
Схлопывание иерархии:
Для достижения 2-й поставленной цели нам необходимо рассчитать несколько мер, начиная с определения нижнего уровня вложенности:
Depth Value =
ISFILTERED ( 'Staff Hierarhy'[1st level] ) +
ISFILTERED ( 'Staff Hierarhy'[2nd level] ) +
ISFILTERED ( 'Staff Hierarhy'[3rd level] )
Место каждого сотрудника в иерархическом дереве определяется при помощи следующей конструкции:
Hierarhy Value =
MAX ( 'Staff Hierarhy'[Hierarchy Level] )
Рассчет суммы по каждому участнику производится по формуле:
Subtotal Amount =
IF (
[Depth Value] > [Hierarhy Value] + 1,
BLANK (),
IF (
[Depth Value] = [Hierarhy Value] + 1,
IF (
AND (
VALUES ( 'Staff Hierarhy'[End Link] ) = FALSE,
SUM ( 'Dataset'[Amount] ) <> 0
),
SUM ( 'Dataset'[Amount] ),
BLANK ()
),
SUM ( 'Dataset'[Amount] )
)
)
Устранение дубликатов в иерархическом дереве со сдвигом промежуточных итогов реализуется следующим образом:
Final Amount =
IF ( [Depth Value] > [Hierarhy Value], BLANK (), SUM ( 'Dataset'[Amount] ) )
Визуализация данных - настроенное поведение иерархии:
Используя стандартный элемент “Matrix” (“Матрица”), и поставив в качестве значения меру “Final Amount” мы можем получить правильный, а также приятный глазу результат:
При этом суммирование по уровню иерархии, так называемые промежуточные итоги, включает в себя как подчиненные элементы, так и свое собственное значение.
Рвав-рвав, наконец-то конец, до новых встречь в Новом году!
Ваш Смайл