Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как ранжировать значения по группам в Excel?

RANKX — функция ранжирования (ранга) в DAX (Power BI и Power Pivot)

Содержание статьи: (нажмите для перехода к соответствующей части статьи):

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы рассмотрим DAX функцию RANKX, создающую ранги среди значений (ранжирование) в Power BI или Power Pivot.

Рассмотрим параметры этой функции, ее свойства и пример формул.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция RANKX в Power BI и Power Pivot

RANKX () — возвращает ранг выражения, вычисляемого в рамках текущего контекста исходного списка значений для каждой строки текущей таблицы.

  • Таблица — исходная таблица или табличное выражение
  • Выражение — любое выражение, вычисляемое для каждой строки исходной таблицы с целью формирования полного списка возможных значений для ранжирования
  • Значение — (необязательный параметр) любое выражение, ранг которого необходимо найти. По умолчанию, если этот параметр не указан, вместо этого параметра возвращается значение выражения из текущей строки
  • Порядок — (необязательный параметр) вид ранжирования параметра «Значение»:
    1) DESC — упорядоченный порядок рангов по убыванию (по умолчанию);
    2) ASC — по возрастанию
  • Равные Значения — (необязательный параметр) способ определения ранга при наличии равных значений:

Пример формулы на основе DAX функции RANKX

В Power BI Desktop имеется исходная таблица «Продажи Менеджеров»:

Исходная таблица в Power BI

Исходя из уровня продаж, вам необходимо ранжировать менеджеров. Другими словами, вы должны создать ранг продаж, где ранг 1 соответствует самому высокому уровню продаж, а ранг 2 и ниже — более низким уровням продаж.

Давайте воспользуемся функцией DAX RANKX и попробуем с ее помощью закодировать формулу для меры [Rank]:

В этой формуле, согласно синтаксису функции RANKX, мы использовали только 2 обязательных параметра, остальные (опциональные) мы не писали.

В качестве первого параметра мы прописали ссылку на исходную таблицу «Продажи Менеджеров».

Во втором параметре мы указали желаемое выражение, которое должно быть вычислено для каждой строки исходной таблицы, указанной в первом параметре. Мы просуммировали значения в столбце [Продажи] с помощью функции SUM (функция суммы в языке DAX).

Как работает мера ранжирования в отчетах Power BI:

Неправильный результат работы функции RANKX

Как видно из визуализации в Power BI, формула на основе функции RANKX работала некорректно, и каждый менеджер получил одинаковый ранг, равный 1.

Существует 2 причины возникновения данной ошибки:

  1. В этой визуализации при расчете конкретной ячейки ранга, значение столбца [Менеджер] является фильтром исходной таблицы.Например, рассмотрим строку с менеджером Петров — при расчете ранга Петрова, исходная таблица будет автоматически отфильтрована только этим менеджером и сумма продаж возвратится только по одному Петрову.Соответственно, ранг рассчитается только исходя из одного этого менеджера и, естественно, ранг будет равен 1. И так по каждому менеджеру.Решить эту проблему мы сможем при помощи еще одной функции языка DAX — ALLSELECTED (прочитать информацию об этой функции Вы можете в этой статье), обернув в эту функцию исходную таблицу из первого параметра. Этим самым, мы удалим все фильтры, которые наложились на столбец [Менеджер] строками самой визуализации.
  2. Так как выражение во втором параметре вычисляется по каждой строке исходной таблицы, указанной в первом параметре, то необходимо передать контекст этой строки из первого параметра во второй параметр.Но, проблема в том, что функция SUM не принимает контекст строки.Чтобы решить эту вторую проблему, нужно обернуть функцию SUM в другую DAX функцию — CALCULATE, которая сможет передать контекст строки из таблицы в выражение. И тогда функция SUM рассчитает сумму продаж в рамках этого контекста строки, то есть, рассчитает сумму продаж только по одному конкретному менеджеру.

Используя эти два пункта, мы можем исправить нашу формулу:

Давайте попробуем выполнить это расширенное измерение в Power BI:

Результат работы формулы в Power BI на основе DAX функции RANKX

Теперь наша мера для подсчета рейтинга продаж менеджеров работает правильно. Продажи менеджера Воснецова составляют 530745, что является наибольшей суммой, и поэтому он занимает первое место по продажам. Менеджер Петров имеет наименьшую сумму продаж — 120 000, поэтому он занимает 4-е место по продажам.

Если же мы хотим поменять порядок ранжирования, чтобы у самой минимальной суммы был ранг 1, а у самой максимальной суммы — ранг 4, то в параметрах функции RANKX нужно добавить 4 параметр «Порядок» и поставить у него значение ASC (по возрастанию). Тогда формула будет такой:

Исходя из синтаксиса функции RANKX в этой формуле в параметрах мы указали 3 параметра — первый, второй и четвертый. Третий и пятый мы пропустили. Этим самым и объясняется лишняя точка с запятой перед ASC в новой измененной формуле, то есть, это и есть пропущенный необязательный третий параметр.

Рассмотрим результат в Power BI:

Результат работы функции RANKX по возрастанию

Теперь, как мы видим, ранг 1 соответствует самой минимальной сумме продаж, а ранг 4 — самой максимальной.

Вот и все, с помощью DAX-анализа функции RANKX, создающей ранги (классификацию) в Power BI или Power Pivot, все готово.

Пожалуйста, оцените эту статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

[Экспресс-видеокурс] Быстрый старт в языке DAX

Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

Если у вас есть вопросы по содержанию этой статьи, пожалуйста, не стесняйтесь задавать их в комментариях ниже. Я обязательно отвечу на них. Более того, оставляйте там свои комментарии, и я буду рад получить от вас ответ.

Также поделитесь этой статьей со своими друзьями в социальных сетях, возможно, этот материал будет им полезен.

Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Что еще посмотреть / почитать?

DAX функции ISFILTERED и ISCROSSFILTERED в Power BI

Как проверить столбец в Power BI (Power Pivot) на наличие фильтров? DAX функции ISFILTERED и ISCROSSFILTERED

DAX функции UNION, INTERSECT и EXCEPT

Функции объединения таблиц в DAX: UNION, INTERSECT и EXCEPT в Power BI и Power Pivot

DAX функции DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR в Power BI

Как в Power BI и Power Pivot сдвинуть даты? DAX функции DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR

Добавить комментарий

Добрый день.
Необходимо вывести место в рейтинге Филиала.
Использую формулу:
Рейтинг филиалов:=RANKX(ALL(‘Филиал'[наименование]);[отклонение];;ASC;Dense)
(где, [отклонение] — мера)
Результат корректный, по всем Филиалам в группе.
Когда выбираю один из Филиалов результат по части из них меняется.
Например, 12 место становится 13 и т.п.
Заранее спасибо.

К сожалению, я не понял исходную информацию, но возможно проблема в функции ALL, попробуйте заменить ее функцией ALLSELECTED.

Огромное спасибо! Русскоязычное сообщество такого уровня (по довольно редкой теме BI) — просто находка.
Теперь я надолго с вами ))

Давайте рассмотрим, как на практике провести ABC-анализ продаж товаров в Excel для магазина мобильных телефонов. Для этого нам нужны названия всех продуктов (групп продуктов) и их коэффициенты рентабельности. На рисунке ниже показана номенклатура продукции и нормы возврата для каждого типа.

ABC анализ продаж продукции предприятия в Excel

Номенклатура продукции для проведения ABC-анализа в Excel

Далее необходимо отсортировать товары по прибыльности. Нажмите «Данные», а затем «Сортировка» в Excel. В результате на первом месте окажутся наиболее прибыльные группы товаров, а затем наименее прибыльные.

Следующий шаг — определение пропорции для каждого вида продукции. Для этого нам необходимо использовать формулы в Excel.

Доля продаж каждого вида товара =B5/СУММ($B$5:$B$15)

Пример проведения ABC анализа продаж в Excel

Определение доли продуктов в продажах компании

На следующем этапе доля группы рассчитывается суммарно по формуле :

Доля товаров в номенклатуре нарастающим итогом =С6+D5

Пример расчета ABC-анализа в Excel для компании

Оценка кумулятивной доли прибыли для группы товаров

Тогда предел должен быть установлен на уровне 80% для группы продуктов «А», 80-95% для группы продуктов «В» и 95-100% для группы продуктов «С». На рисунке ниже показан результат группировки товаров в три группы для розничного продавца мобильных телефонов. Например, на Samsung, Nokia, Fly и LG приходится 80% от общего объема продаж, на Alcatel, HTC и Lenovo — 15% продаж, а на Philips, Sony, Apple и ASUS — 5% продаж.

ABC анализ продаж продукции. Пример расчета в Excel

ABC анализ продаж продукции. Пример расчета в Excel

Сгруппировав товары, компания получает аналитический отчет о том, какие товары обеспечивают наибольший денежный поток. Другая цель — увеличить продажи целевых товаров в группе А и уменьшить долю неэффективных товаров в группе С. В нашем примере это примерно.

30% товаров приносят 80% прибыли компании.

Исходные данные для примеров

Мы будем использовать MS SQL Server Express 2014 и писать запросы в Management Studio Express. Мы будем использовать таблицу, продающую тестовые данные, которая будет содержать различные товары (телефоны, планшеты, ноутбуки, программное обеспечение) с вымышленными ценами.

Наша таблица испытаний

Давайте заполним его тестовыми данными и в итоге получим следующее (для выбора мы напишем простой селективный запрос)

Скриншот 1

Как использовать матрицу Эйзенхауэра

Этот инструмент подходит для тех, кто готов и способен оценить важность своих задач и четко их классифицировать. Метод предполагает разбивку задач и действий на четыре группы:

  1. Срочно и важно;
  2. Важно, но не срочно;
  3. Срочно, но не важно;
  4. Ни срочно, ни важно.

Метод Эйзенхауэра направлен на то, чтобы отсеять второстепенные вопросы от важных решений, чтобы найти то, что является действительно важным.

Если представить матрицу Эйзенхауэра в виде таблицы, то она будет выглядеть так

Значение квадрантов в матрице

Задачи назначаются на определенные квадранты, которые в свою очередь определяют, когда и как долго вы можете выполнять задачу.

  • Квадрант I — «Сделай это сейчас» (срочный и важный)

Задачи здесь требуют немедленного внимания. Они имеют жесткие сроки выполнения и должны быть выполнены прежде всего и лично.

  • Квадрант II — «Решите, когда это сделать» (важно, но не срочно)

Этот квадрант является стратегической частью матрицы и идеально подходит для долгосрочного развития. Содержащиеся в нем элементы важны, но не требуют немедленных действий. В то же время, эти задания имеют ограничение по времени и выполняются лично.

  • Квадрант III — «Делегировать кому-то» (срочный, но не важный)

Квадрант встреч и мероприятий включает в себя электронную почту, телефонные звонки и составление расписания. Поскольку эти задачи не связаны с измеримыми результатами, они не требуют личного внимания. С помощью квадранта III вы можете свести к минимуму то, что отвлекает вас от важных задач. Делегируя обязанности, вы сможете уделять внимание более важным задачам.

  • Квадрант IV — «Сделаем это позже» (не важно, не срочно)

Действия, которые попадают в квадрант IV, являются случайными делами, не приносящими никакой пользы. Это просто вещи, которые всегда можно отложить на потом, не опасаясь никаких последствий. Эта деятельность отнимает много времени и мешает выполнению более важных задач, которые вы разместили в первых двух квадрантах.

Выбор цвета для матрицы

Каждому квадранту матрицы можно присвоить цвет и уровень приоритетности.

Желтый цвет означает важность, но не безотлагательность.

Зеленый цвет = срочно, но не важно.

Серый = не срочно, не важно.

Используя матрицу в профессиональных целях, вы обнаружите, что большинство задач попадает в квадранты I и III. Деятельность в квадранте II дает самые значительные результаты, поскольку это бизнес-цели, которые влияют на долгосрочный успех компании и редко классифицируются как срочные.

Труднее всего понять, что отвлекает вас от намеченного курса. Но если вы решите эту фундаментальную проблему управления временем, вы избавите себя от мысли о зря потраченных часах. Задайте себе два вопроса, чтобы определить свои долгосрочные стратегии принятия решений:

  • Когда вы будете находить время для важных, но не срочных дел?
  • Когда вы можете найти время для важных дел, прежде чем они внезапно станут срочными?

Стоит помнить, что задачи из одного квадранта иногда неожиданно попадают в другой квадрант. Если возникнет чрезвычайная ситуация, ваши приоритеты изменятся. Например, вы владеете небольшим бизнесом, и вам звонит недовольный клиент и просит связаться с начальником из-за задержки доставки. Эта проблема сразу же станет больше, чем другие элементы матрицы.

Разделение задач на квадранты имеет некоторые особенности, которые необходимо учитывать:

  1. Списки дел облегчают жизнь. Убедитесь, что при распределении задач вы задаете правильные вопросы, которые помогут вам определить, что нужно сделать в первую очередь. Ключевым атрибутом является приоритет.
  2. Вы можете добавить много видов деятельности и задач в каждый квадрант, но лучше не превышать восьми пунктов. В противном случае вы будете отвлекаться от главной цели — выполнения задания.
  3. Создайте отдельные матрицы для рабочей и личной жизни.
  4. Только вы можете определить уровень приоритетности пунктов вашего списка. Начинайте каждое утро со списка задач из матрицы, и в конце недели вы увидите результат.

Шаблон матрицы Эйзенхауэра

Чтобы упростить процесс распределения задач, используйте шаблон, разработанный Evernote:

Матрица Эйзенхауэра может быть переведена в программное обеспечение для управления проектами Trello. Составьте список дел для каждой из четырех досок (= квадрантов) и сделайте отдельную доску «Входящие», куда будут помещаться все задачи, прежде чем они будут распределены по квадрантам. Таким образом, вы сможете визуально оценить свою нагрузку.

Матрица Эйзенхауэра поможет вам избежать аналитического паралича, когда вы не знаете, с чего начать.

Не пропустите новые публикации

Подпишитесь на рассылку, и мы поможем вам понять требования законодательства, научим, что делать при возникновении проблем, и расскажем, как зарабатывать больше.

голоса
Рейтинг статьи
Читайте так же:
Как преобразовать десятичные градусы в градусы минут секунды в Excel?
Ссылка на основную публикацию
Adblock
detector