Оценка KPI персонала в Power BI

Юрий Мацегора
Follow me

Юрий Мацегора

Business analyst at Smart-Metrika
Бизнес - аналитик, а также автор и редактор на сайте, если у Вас есть вопросы о содержании и материалах статей пишите мне:)
Юрий Мацегора
Follow me

Практическая статья об использовании Microsoft Power BI для оценивания KPI сотрудников и создания единого рейтинга эффективности работников, основанного на нормализации данных. Файлы с примерами использования в формате .xlsx для Excel и .pbi для Microsoft Power BI Desktop - в конце статьи. Формулы и пояснения к ним даны в первой части. Для полного понимания происходящего важно ознакомиться с теоретическими аспектами, но можно и сразу приступить к созданию рейтинга (не рекомендуется).

Первая часть Оценка показателей эффективности сотрудников

Один из вариантов визуализации конечного результата.

Считаем KPI в Excel

 

Задание руководства: необходимо составить рейтинг сотрудников. KPI  состоит из 5 показателей (далее - переменных):

  •  - Количество продаж - количественная переменная, единица измерения - кол-во, период взят за 3 месяца. Линейная зависимость  - чем больше продаж, тем выше рейтинг
  •  - Количество отработанных часов - количественная переменная, измеряется в часах, период тот же. Прямая зависимость
  •  - Сумма продаж - количественная переменная, единица измерения - $, период 3 месяца. Прямая зависимость
  •  - Количество опозданий - количественная переменная, единица измерения в учебном примере - кол-во. Обратная зависимость - чем больше значение, тем меньше рейтинг
  •  - Наличие высшего образования - качественная дихотомическая переменная, подразумевает 2 значения: ответ - да = "2", а ответ - нет  = "1". Ответ "да" повышает значение в рейтинге.

Найдем среднее значение и стандартное отклонение для переменных и перейдем к нормализации.

Для расчета стандартного отклонения используем формулу "СТАНДОТКЛОН.Г", а не "СТАНДОТКЛОН.В".

Буква "Г" указывает на работу с генеральной совокупностью, а "В"  - с выборкой. В случае работы с выборкой из знаменателя дополнительно вычитается 1. Мы работаем с генеральной совокупностью.

Нормализация & Т-рейтинг

 

Разбор формулы стандартизации - в прошлой статье.

В Excel, в отличие от Power BI, есть отдельная функция для нормализации данных.

Прописываем формулы стандартизации для каждой переменной и получаем 5 столбцов:

Z_1(можно протянуть на все 5 столбцов)

=НОРМАЛИЗАЦИЯ(B2;B$18;B$19)

Z_2

=НОРМАЛИЗАЦИЯ(C2;C$18;C$19)

Z_3

=НОРМАЛИЗАЦИЯ(C2;C$18;C$19)

Z_4

=НОРМАЛИЗАЦИЯ(C2;C$18;C$19)

Z_5

=НОРМАЛИЗАЦИЯ(C2;C$18;C$19)

Формулы прописаны в учебном примере.

Теперь переменные "нормализованы". Если среднее значение в каждом показателе эффективности = 0, а стандартное отклонение = 1 - всё сделано правильно.

Данные, вне зависимости от того количественные они или качественные, приобрели единый вид - поэтому процесс и получил такое название.

Переходим к расчету Т-рейтинга каждой переменной:

Т - рейтинг = Z x 10 + 50

Снова 5 столбцов:

Т - рейтинг_1

=I2*10+50

Т - рейтинг_2

=J2*10+50

Т - рейтинг_3

=K2*10+50

Т - рейтинг_4 - переменная с обратной зависимостью (чем больше количество опозданий, тем меньше значение рейтинга). Поэтому вместо умножения на 10 умножаем на (-10)

=((L2*(-10))+50)

Т - рейтинг_5

=M2*10+50

Если среднее значение = 50, а стандартное отклонение = 10 - значит все сделано правильно.

Остается сложить все значения и получить одну оценку Т-рейтинга

=N2+O2+P2+Q2+R2

Если не нужны промежуточные расчеты, и не хотите видеть рейтинг по отдельным переменным, запишите все одной формулой

=(НОРМАЛИЗАЦИЯ(B2;СРЗНАЧ($B$2:$B$17);СТАНДОТКЛОН.Г($B$2:$B$17))*10+50)+(НОРМАЛИЗАЦИЯ(C2;СРЗНАЧ($C$2:$C$17);СТАНДОТКЛОН.Г($C$2:$C$17))*10+50)+(НОРМАЛИЗАЦИЯ(D2;СРЗНАЧ($D$2:$D$17);СТАНДОТКЛОН.Г($D$2:$D$17))*10+50)+(НОРМАЛИЗАЦИЯ(E2;СРЗНАЧ($E$2:$E$17);СТАНДОТКЛОН.Г($E$2:$E$17))*(-10)+50)+(НОРМАЛИЗАЦИЯ(F2;СРЗНАЧ($F$2:$F$17);СТАНДОТКЛОН.Г($F$2:$F$17))*10+50)

 

Анализ эффективности в Power BI

 

Адаптируем модель KPI работников для Power BI Desktop с помощью DAX. Сделать это проще, чем в Excel, несмотря на то, что в Power BI нет отдельной формулы для нормализации.

С помощью Power Query получаем исходные данные из таблицы:

Создавать будем расчетные столбцы.

В примере с Power BI промежуточных значений Z не будет, включим нормализованное значение в Т-рейтинг.

Т-рейтинг_1

Т-рейтинг_1 = (('Лист1'[Кол-во продаж_1] - AVERAGE('Лист1'[Кол-во продаж_1]))/STDEV.P('Лист1'[Кол-во продаж_1])) *10 + 50

Т-рейтинг_2

T-рейтинг_2 = ((('Лист1'[Отработано часов_2] - AVERAGE('Лист1'[Отработано часов_2]))/STDEV.P('Лист1'[Отработано часов_2]))) * 10 + 50

Т-рейтинг_3

Т-рейтинг_3 = (('Лист1'[Сумма продаж_3] - AVERAGE('Лист1'[Сумма продаж_3]))/STDEV.P('Лист1'[Сумма продаж_3]))*10+50

Т-рейтинг_4

Т-рейтинг_4 = (('Лист1'[Кол-во опозданий_4] - AVERAGE('Лист1'[Кол-во опозданий_4]))/STDEV.P('Лист1'[Кол-во опозданий_4])) * (-10) + 50

Т-рейтинг_5

Т-рейтинг_5 = (('Лист1'[Наличие высшего_5]- AVERAGE('Лист1'[Наличие высшего_5]))/STDEV.P('Лист1'[Наличие высшего_5]))*10+50

Общая оценка

Т-рейтинг = 'Лист1'[Т-рейтинг_1]+'Лист1'[T-рейтинг_2]+'Лист1'[Т-рейтинг_3]+'Лист1'[Т-рейтинг_4]+'Лист1'[Т-рейтинг_5]

Можно прописать одной формулой

Одна_формула = (((('Лист1'[Кол-во продаж_1] - AVERAGE('Лист1'[Кол-во продаж_1]))/STDEV.P('Лист1'[Кол-во продаж_1])) *10 + 50)) + (((('Лист1'[Отработано часов_2] - AVERAGE('Лист1'[Отработано часов_2]))/STDEV.P('Лист1'[Отработано часов_2]))) * 10 + 50) + (((('Лист1'[Сумма продаж_3] - AVERAGE('Лист1'[Сумма продаж_3]))/STDEV.P('Лист1'[Сумма продаж_3]))) * 10 + 50) + (((('Лист1'[Кол-во опозданий_4] - AVERAGE('Лист1'[Кол-во опозданий_4]))/STDEV.P('Лист1'[Кол-во опозданий_4])) * (-10) + 50)) + (((('Лист1'[Наличие высшего_5] - AVERAGE('Лист1'[Наличие высшего_5]))/STDEV.P('Лист1'[Наличие высшего_5])) *10 + 50))

Мои поздравления - рейтинг составлен. Можете добавить KPI своих сотрудников и смотреть на "крысиные бега".

Последним пунктом остается визуализация результатов. Плюс Т-рейтинга в том, что он позволяет на одном графике одновременно сравнивать все переменные, в том числе качественные.

В обычном виде данных сделать это нельзя.

* * *

В заключение стоит сказать, а говорю я это как наемный работник.

Если Вы начальник и любите своих подопечных подчиненных - закройте эту страницу).


**При подготовке статьи использовалась официальная документация и сопутствующие материалы по Power BI Desktop, а также наработки компании Smart-Metrika.

Ссылка на скачивание в Excel

Ссылка на скачивание  в Power BI Desktop