Диаграмма каскадная в excel

Диаграмма водопад (waterfall chart) в Excel

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

Диаграмма водопад (также называют waterfall chart) выглядит следующим образом:

Диаграмма водопад (waterfall chart)

Построение водопадной диаграммы

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


Чтобы построить диаграмму добавим в таблицу вспомогательные элементы:

Построение вспомогательной таблицы

  • Прозрачный — вспомогательный столбец поднимающий факторы на необходимую высоту;
  • Красный 1 — начальное значение;
  • Оранжевый — отрицательный фактор;
  • Зеленый — положительный фактор;
  • Красный 2 — конечное значение.

Расшифровка формул вспомогательной таблицы

Построение накопительной гистограммы

Меняем цвет рядов диаграммы

Выставляем боковой зазор для рядов равным 0

Добавляем подпись данных на ряды

Далее форматируем диаграмму на свое усмотрение и получаем окончательный вид (убираем легенду, подпись вертикальной оси и т.д.):

Диаграмма водопад (waterfall chart)

Особенности диаграммы водопад

В случае, если факторы диаграммы переходят через ноль (т.е. меняют положительное значение на отрицательное), то данный способ построения, к сожалению, не срабатывает.
Excel при построении накопительной гистограммы откладывает столбцы либо от 0, либо от положительно значения. Поэтому могут возникнуть ошибки следующего вида:

Источник: tutorexcel.ru

Создание каскадной диаграммы

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

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

Создание каскадной диаграммы

Щелкните Вставка > Вставить каскадную или биржевую диаграмму > Каскадная.

Для создания каскадной диаграммы также можно использовать вкладку Все диаграммы в разделе Рекомендуемые диаграммы.

Совет: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, и на ленте появится область Работа с диаграммами.

Итоги и промежуточные итоги с началом на горизонтальной оси

Если данные содержат значения, которые считаются промежуточными и итоговыми, например нетто Income, вы можете установить эти значения таким образом, чтобы они начинались с горизонтальной оси, а не “Плавающая”.

Дважды щелкните точку данных, чтобы открыть область задач Формат точки данных , и установите флажок установить как итог .

Примечание: Если щелкнуть столбец один раз, будет выбран ряд данных, а не точка данных.

Чтобы снова сделать столбец плавающим, снимите флажок Задать как итог.

Совет: Итоги также можно задать, щелкнув правой кнопкой мыши точку данных и выбрав в контекстном меню пункт Задать как итог.

Отображение и скрытие соединительных линий

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

Чтобы скрыть соединительные линии, щелкните правой кнопкой мыши ряд данных, чтобы открыть область задач ” Формат ряда данных “, и снимите флажок Показывать соединительные линии .

Чтобы снова отобразить эти линии, установите флажок Отображать соединительные линии.

Совет: В легенде диаграммы точки данных сгруппированы по типам: Увеличение, Уменьшение и Итог. Если щелкнуть легенду диаграммы, на диаграмме будут выделены все столбцы, соответствующие выбранной группе.

Ниже показано, как создать каскадную диаграмму в Excel для Mac.

На вкладке Вставка на ленте щелкните (значок каскада) и выберите Каскад.

Примечание: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, чтобы отобразить их на ленте.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник: support.office.com

Диаграмма каскадная в excel

Войти через uID

В арсенале MS Excel или Minitab найдется более полусотни различных диаграмм. Тем не менее, одного из самых популярнейших способов визуализации данных – каскадной диаграммы – там не отыскать. Разумеется, можно прибегнуть к помощи специализированных программ или надстроек для MS Excel. Однако красивые и функциональные решения, вроде think-cell, обойдутся недешево, а бесплатные варианты, как plusx, вряд ли можно назвать профессиональным решением.

В любом случае, если вы привыкли обходиться только тем ПО, которое всегда под руками, то эта статья – именно то, что вам нужно. Из нее вы узнаете, как построить каскадную диаграмму даже без наличия дополнительных надстроек в MS Excel и Minitab.

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

Содержание:

1. Что такое диаграмма Waterfall?

Waterfall переводится как водопад. Можно было бы назвать Waterfall-диаграмму графиком водопада, но специалисты из финансовой области скорее знают ее как bridge (мост) или каскадную диаграмму. Хотя не исключено, что вы можете встретить термин “диаграмма водопада”, “диаграмма мост” или “летающие кирпичи”. Как только не называют этот график.

Каскадная диаграмма – это графическая визуализации совокупности факторов, оказывающих положительные и отрицательные эффекты на общий результат:

К примеру, с помощью диаграммы “водопад” вы можете визуализировать динамику семейного бюджета за определенный период:

2. Когда применять, а когда не применять Waterfall?

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

Вот некоторые примеры:

  • Выполнение производственного плана (план, факт и все эффекты, повлиявшие на разницу).
  • Поток наличности – cash-flow (все поступления, все расходы, результат).
  • Анализ простоев (общее время, плановые простои, доступное время, неплановые простои, результат).
  • Возврат или окупаемость инвестиций (вложенные деньги, расходы, прибыль).

Теоретически, с помощью диаграммы “водопад” можно изобразить любую динамику. К примеру, динамику посещаемости нашего сайта (среднесуточное количество посетителей по месяцам):

Хоть этого никто и не запрещает, но в данном случае лучше воспользоваться графиком временного ряда (Time Series), а не каскадной диаграммой. Дело в том, что главная идея Waterfall-диаграммы в том, чтобы показать, как те или иные факторы повлияли на конечный результат. В случае с посещаемостью сайта, посещаемость в июле 2016 года вряд ли повлияла на посещаемость в декабре того же года или на июль следующего года.

Читайте также:  Как сложить одинаковые позиции в excel

3. Как построить Waterfall-диаграмму в Minitab?

Лично я использую разные способы построения диаграммы “водопад” в MS Excel и Minitab. Это, скорее, дело привычки, нежели удобства или скорости выполнения. Вы можете попробовать оба подхода и выбрать тот, который будет удобнее для вас.

В качестве данных для анализа мы возьмем пример, указанный в начале статьи:

В первую очередь, нам потребуется перенести все цифры на лист:

Весь трюк в том, чтобы верно внести нужные данные:

  • Исходную ситуацию и результат достаточно внести только 1 раз на лист.
  • Все положительные и отрицательные эффекты следует внести дважды.
    • Например, высота “Положительный эффект” составляет 120. При этом следует указать высоту предыдущего столбца и самого эффекта отдельно.
    • Высота столбца “Отрицательный эффект” также составляет 120. Однако в данном случае следует вторым значением указать, на сколько понизился столбец (40), а первым – разницу между исходным значением и итоговым (120-40=80).

Чтобы программа могла отличить наши значения, потребуется указать дополнительные атрибуты. В блоге Minitab-а, предлагают это сделать по-своему, но я это делаю несколько по-другому:

  • Всем исходным и результирующим значениям, а также значениям эффектов, присваиваем атрибут “a”.
  • Всем остальным значениям – “b”.

Затем в меню Graph выберите Bar chart. В появившемся окне выберите вначале опцию Values from a table, а затем кликните на Stack в рядке One column of values:

Нажмите ОК и укажите в следующем окне переменные в поле Graph variables и атрибуты – в следующем поле. Обратите внимание на очередность указания колонок с атрибутами:

Чтобы поменять синие и красные столбцы местами, кликните дважды по любой колонке (курсор должен находиться именно на колонке, а не просто в любо месте диаграммы) и перейдите на вкладку Chart Options:

Переместите флажок с Bottom of stack на Top of stack, как показано на картинке выше. Нажмите ОК:

Уже ближе к исходному варианту, но понадобится еще немного мануальных настроек:

    Выделите красные колонки, затем дважды щелкните на них и в появившемся окне установите отсутствие заливки и рамки фигуры:

Выделите положительный эффект, дважды кликните и установите зеленую заливку:

Выделите отрицательный эффект, дважды кликните и установите красную заливку:

  • Уберите все лишние подписи и данные, настройте ширину столбцов, добавьте подписи данных, настройте шкалы… Вот теперь диаграмма готова:
  • Как построить Waterfall-диаграмму в MS Excel?

    Как я писал выше, я использую разные способы построения диаграммы “водопад” в MS Excel и Minitab. Вы можете применить тот же подход или попробовать другой – из примера выше, с построением диаграммы в Minitab.

    Мы используем те же данные, но не будем вносить эффект дважды и указывать дополнительные атрибуты. Вместо этого мы создадим 2 новые колонки:

    • для построения графика;
    • для построения невидимого графика.

    Перенесем без изменений первое и последнее значения из колонки “Данные” в колонку “График”. Все промежуточные значения рассчитаем по формуле:

    В колонке “Невидимка” первое и последнее значения умышленно оставим пустыми. Все промежуточные значения рассчитаем по формуле:

    Нам понадобится вот эта диаграмма:

    Кликнув по любой колонке дважды, вы откроете новое диалоговое окно. В этом окне установите ползунок перекрытия на 100% и передвиньте ползунок щели, например, на значение 25%:

    Закройте окно. Затем выделите ряд “Невидимки” и кликните по нему 2 раза. В открывшемся окне перейдите на вкладку “Заливка” и установите белую заливку:

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

    Как видите, большинство графиков для этого материала выполнено вторым способом. На мой взгляд, он более гибкий и поддающийся автоматизации. Однако требует от вас применения разных формул для разных случаев (наборов данных). Тем не менее, я надеюсь, что хоть один из способов вам подойдет, а если вы используете свой собственный, то поделитесь им с читателями, используя форму комментариев ниже. Или пришлите мне описание вашего метода, а я опубликую его в блоге нашего сайта.

    Источник: sixsigmaonline.ru

    Как построить диаграмму “водопад” (waterfall)

    Все чаще и чаще встречаю в отчетности разных компаний и слышу просьбы от слушателей на тренингах объяснить как строится каскадная диаграмма отклонений – она же “водопад”, она же “waterfall”, она же “мост”, она же “bridge” и т.д. Выглядит она примерно так:

    Издали действительно похожа на каскад водопадов на горной реке или навесной мост – кто что видит 🙂

    Особенность такой диаграммы том, что:

    • Мы наглядно видим начальное и конечное значение параметра (первый и последний столбцы).
    • Положительные изменения (рост) отображаются одним цветом (обычно зеленым ), а отрицательные (спад) – другим (обычно красным ).
    • Иногда в диаграмме могут присутствовать ещё и столбцы промежуточных итогов ( серые , приземленные на ось Х столбцы).

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

    • Наглядное отображение динамики какого-либо процесса во времени: потока наличности (cash-flow), инвестиций (вкладываем деньги в проект и получаем от него прибыль).
    • Визуализация выполнения плана (крайний левый столбик в диаграмме – факт, крайний правый – план, вся диаграмма отображает наш процесс движения к желаемому результату)
    • Когда нужно наглядно показать факторы, влияющие на наш параметр (факторный анализ прибыли – из чего она складывается).

    Есть несколько способов построения такой диаграммы – всё зависит от вашей версии Microsoft Excel.

    Способ 1. Самый простой: встроенный тип в Excel 2016 и новее

    Если у вас Excel 2016, 2019 или новее (или Office 365), то построение такой диаграммы не составит труда – в этих версиях Excel такой тип уже встроен по умолчанию. Нужно будет лишь выделить таблицу с данными и выбрать на вкладке Вставка (Insert) команду Каскадная (Waterfall) :

    В результате мы получим практически готовую уже диаграмму:

    Сразу же можно настроить желаемые цвета заливки для положительных и отрицательных столбцов. Удобнее всего это сделать, выделив соответствующие ряды Увеличение и Уменьшение прямо в легенде и, щёлкнув по ним правой кнопкой мыши, выбрать команду Заливка (Fill) :

    Если нужно добавить в диаграмму столбцы с промежуточными итогами или финальный столбец-итог, то удобнее всего это сделать с помощью функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) или АГРЕГАТ (AGGREGATE) . Они посчитает накопленную с начала таблицы сумму, исключив при этом из нее выше расположенные аналогичные итоги:

    В данном случае, первый аргумент (9) – это код математической операции суммирования, а второй (0) заставляет функцию не учитывать в результатах уже вычисленные итоги за предыдущие кварталы.

    После добавления строк с итогами останется выделить на диаграмме появившиеся итоговые колонки (сделать два последовательных одиночных щелчка по столбцу) и, щёлкнув правой кнопкой мыши, выбрать команду Установить в качестве итога (Set as total) :

    Читайте также:  Как в excel поставить текущую дату

    Выбранный столбец “приземлится” на ось Х и автоматически поменяет цвет на серый.

    Вот, собственно, и всё – диаграмма-водопад готова:

    Способ 2. Универсальный: невидимые столбцы

    Если у вас Excel 2013 или более древние версии (2010, 2007 и т.д.), то описанный выше способ вам не подойдёт. Придется идти обходным путем и выпиливать недостающую каскадную диаграмму из обычной гистограммы с накоплением (суммированием столбиков друг на друга).

    Хитрость тут заключается в использовании прозрачных столбцов-подпорок, приподнимающих наши красные и зеленые ряды данных на нужную высоту:

    Для построения такой диаграммы нам потребуется добавить к исходным данным еще несколько вспомогательных колонок с формулами:

    • Во-первых, нужно разделить наш исходный столбец, выделив положительные и отрицательные значения в разные колонки с помощью функции ЕСЛИ (IF) .
    • Во-вторых, нужно будет добавить перед сделанными столбцами колонку Пустышки, где первое значение будет 0, а начиная со второй ячейки формулой будет вычисляться высота тех самых прозрачных подпирающих столбцов.

    После этого останется выделить всю таблицу кроме исходного столбца Поток и создать обычную гистограмму с накоплением через Вставка – Гистограмма (Insert – Column Chart) :

    Если теперь выделить синие столбцы и сделать их невидимыми (по ним правой кнопкой мыши – Формат ряда – Заливка – Нет заливки), то мы как раз и получим то, что требуется.

    В плюсах подобного способа – простота. В минусах – необходимость считать вспомогательные колонки.

    Способ 3. Если уходим в минус – всё сложнее

    К сожалению, предыдущий способ адекватно работает только для положительных значений. Если хотя бы на каком-то участке наш водопад уходит в отрицательную область, то сложность задачи возрастает в разы. В этом случае необходимо будет формулами просчитать каждый ряд (пустышки, зеленые и красные) отдельно для отрицательной и положительной частей:

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

    Способ 4. Экзотический: полосы повышения-понижения

    Этот способ основан на использовании специального малоизвестного элемента плоских диаграмм (гистограмм и графиков) – Полос повышения-понижения (Up-Down Bars) . Эти полосы попарно соединяют точки двух графиков, чтобы наглядно показать какая из двух точек выше-ниже, что активно используется при визуализации план-факта:

    Легко сообразить, что если убрать линии графиков и оставить на диаграмме только полосы повышения-понижения, то мы получим все тот же “водопад”.

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

    Для создания “водопада” нужно выделить столбец с месяцами (для подписей по оси Х) и два дополнительных столбца График 1 и График 2 и построить для начала обычный график через Вставка – График (Insert – Line Сhart) :

    Теперь добавим к нашей диаграмме полосы повышения-понижения:

    • В Excel 2013 и новее для этого необходимо выбрать на вкладке Конструктор команду Добавить элемент диаграммы– Полосы повышения-понижения (Design – Add Chart Element – Up-Down Bars)
    • В Excel 2007-2010 – перейти на вкладку Макет – Полосы повышения-понижения (Layout – Up-Down Bars)

    Диаграмма после этого начнёт выглядеть примерно так:

    Осталось выделить графики и сделать их прозрачными, щелкнув по ним по очереди правой кнопкой мыши и выбрав команду Формат ряда данных (Format series) . Аналогичным образом можно изменить и стандартные, весьма убого выглядящие, чёрно-белые цвета полос на зелёные и красные, чтобы получить в итоге более приятную картинку:

    В последних версиях Microsoft Excel ширину полос можно изменить, щёлкнув по одному из прозрачных графиков (не по полосам!) правой кнопкой мыши и выбрав команду Формат ряда данных – Боковой зазор (Format series – Gap width) .

    В старых версиях Excel для такого исправления приходилось использовать команду на Visual Basic:

    1. Выделите построенную диаграмму
    2. Нажмите сочетание клавиш Alt + F11 , чтобы попасть в редактор Visual Basic
    3. Нажмите сочтетание клавиш Ctrl + G , чтобы открыть панель прямого ввода команд и отладки Immediate (обычно она расположена внизу).
    4. Скопируйте и вставьте туда вот такую команду: ActiveChart.ChartGroups(1).GapWidth = 30 и нажмите Enter :

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

    Источник: www.planetaexcel.ru

    Exceltip

    Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

    Диаграмма водопад (waterfall chart) в Excel

    Водопад диаграмма (waterfall chart) является одной из форм визуализации данных, которая показывает совокупный эффект последовательно введенных положительных и отрицательных значений. Также иногда можно встретить название bridge chart, или «мост».

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

    Выглядит она следующим образом:

    Итак, посмотрим, как же можно построить диаграмму, похожую на водопад.

    Подготовка данных

    Изначально у вас имеются данные, примерно похожие на таблицу ниже. Чтобы построить водопад, вам предстоит привести их в должный вид.

    Водопад – это обычная диаграмма, отформатированная определенным образом. «Пустоты» под промежуточными значениями – это тоже ряды данных, но только без заливки. Таким образом, нам нужно разбить данные исходной таблицы на четыре колонки: крайние и прозрачные колонки, положительные значения и отрицательные значения. Наша таблица примет вид:

    Где имеются такие формулы

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

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

    Тут все просто, ячейка G3 суммирует значения ячеек C3:E3, соответственно формула в ней будет =СУММ(C3:E3). Ячейка G4 копирует значение ячейки G3.

    Создание диаграммы Водопад

    Осталось самое простое – построить диаграмму. Выделяем ячейки A1:A6 (да, пустую ячейку тоже включаем), жмем клавишу Ctrl и выделяем ячейки C1:J6, таким образом у вас будет выделено две области.

    Переходим по вкладке Вставка в группу Диаграммы, выбираем Вставить гистограмму -> Гистограмма с накоплением. У вас должен получиться вот такой график:

    Меняем значения столбцов и строк местами. Для этого переходим по вкладке Работа с диаграммами -> Конструктор в группу Данные и щелкаем по иконке Строка/столбец. Наша диаграмма примет вид:

    Читайте также:  В эксель заменить

    Щелкаем правой кнопкой по любому ряду данных, из всплывающего меню выбираем Изменить тип диаграммы для ряда. В появившемся диалоговом окне, меняем ряды данных соответствующие коннекторам на График.

    На диаграмме щелкаем правой кнопкой по ряду данных Конектор1, из всплывающего меню выбираем Формат ряда данных, в правой панели во вкладке Линия устанавливаем формат, чтобы получилась пунктирная линия черного цвета. Повторяем эти действия для всех рядов данных отвечающих за коннекторы.

    Далее воспользуемся принципом условного форматирования для диаграмм, который был описан в одном из предыдущих статей. Крайние ряды данных заливаем в серый, Зеленый и красный заливаем соответствующими цветами, у Прозрачного убираем заливку вовсе. Щелкаем правой кнопкой мыши по ряду данных, выбираем Формат ряда данных. В правой панели во вкладке Заливка и граница устанавливаем необходимые опции заливки для конкретного ряда данных.

    В принципе, наша диаграмма водопад готова.

    Осталось добавить кое-какие штрихи в форматирование. Убрать лишние элементы: легенду, линии сетки. Дать диаграмме внятное имя. Добавить подписи данных. В добавок, я уменьшил боковой зазор между колонками. Все наши злоключения должны были привести нас к следующему результату:

    Источник: exceltip.ru

    Пользовательская сортировка каскадной диаграммы (Waterfall) в Power BI

    Недавно в чате о Power BI в Telegram был задан вопрос – возможно ли применить для графика Waterfall (каскадная диаграмма, она же «Водопад») нестандартную динамическую сортировку: положительные значения показываются по убыванию, а отрицательные наоборот, по возрастанию (то есть, сначала самые большие по модулю отрицательные значения, затем минуса поменьше и самые мелкие – в конце).

    В итоге из такого графика:

    Нужно получить вот такой:

    Стандартными средствами мы можем сортировать Waterfall только по возрастанию или убыванию, по обычным правилам (настроить и проверить сортировку можно нажав на три точки в правом верхнем углу визуала):

    Нам же нужно сделать так, чтобы:

    1. Можно было применить нестандартную сортировку;
    2. Она должна быть динамической, то есть реагировать на фильтры (например, по дате)

    Решение этой задачи делится на две подзадачи:

    1. Найти способ применить пользовательскую сортировку
    2. Определить и реализовать алгоритм сортировки

    Можем ли мы применить пользовательскую сортировку?

    Первая подзадача в случае визуального элемента Waterfall (каскадная диаграмма) решается просто: мы можем использовать для сортировки любые поля, помещенные в одну из областей визуального элемента.

    У Waterfall таких областей четыре:

    Первые две используются для определения значений оси X, третья – для определения размера столбиков, а вот четвертая используется для вывода информации во всплывающем окошке при наведении мыши на элемент графика.

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

    Скажу сразу – если бы мы не могли использовать эту область для сортировки, то и решение было бы кардинально другим, если бы вообще было.

    Как задать правило сортировки?

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

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

    Вспоминаем вводные условия:

    1. Если значение положительное, то мы должны использовать обычный порядок – например, от большего к меньшему
    2. Если значение отрицательное, то порядок должен быть обратным – от меньшего к большему.

    Самым распространенным способом создания динамической сортировки является использование функции RANKX в формуле меры.

    Давайте посмотрим на исходные данные, отсортированные в обычном порядке:

    Мера Simple Rank, использованная в таблице – это обычный RANKX, рассчитанный на основе меры [Amount] (в данном случае не важно, что именно считает эта мера, главное, что нам нужно сортировать ее значения).

    Simple Rank =
    IF (
    ISINSCOPE ( ‘Margin Details'[SubArticle] ) ;
    RANKX ( ALLSELECTED ( ‘Margin Details'[SubArticle] ) ; [Amount]; [Amount]; ASC )
    )

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

    Так как в задаче было описано условие, что отрицательные числа должны сортироваться по убыванию модуля, то первым порывом был расчёт модуля значений меры [Amount] при помощи функции ABS. Однако, если брать в расчёт не только отрицательные, но и положительные значения (а у нас именно такой случай), их модули перемешиваются, и мы получим кашу из + и – значений.

    Еще одним вариантом решения было использование функции IF внутри функции RANKX. Но, перепробовав несколько вариантов, я окончательно запутался в контекстах вычисления, оставил эту затею и вернулся к переосмыслению задачи.

    Давайте посмотрим на ряд чисел, отсортированный по убыванию:

    Если мы присвоим им ранг по возрастанию значения (самое маленькое число имеет самый маленький по номиналу ранг), то мы получим вот такой ряд рангов:

    Нам надо как-то сделать, чтобы самое маленькое отрицательное число имело самый большой ранг среди отрицательных чисел.

    В итоге после некоторой медитации на табличку с рангами и значениями решение пришло само. Как всегда, оно лежало на виду, надежно прикрытое привычкой считать, что ранг – это ряд положительных чисел, который начинается, например, с 1.

    На самом деле ранг может быть представлен любым числом, в том числе отрицательным. Отрицательный ранг будет всегда меньше даже самого маленького положительного ранга. Значит, если ранг отрицательного числа сам будет отрицательным, то отрицательные числа точно будут идти после положительных.

    Половину проблемы мы решили. Осталось только «перевернуть» ранг отрицательных. Но это сделать уже очень просто. В DAX, как и в Excel, есть непримечательная функция SIGN (в русскоязычных версиях Excel она называется ЗНАК), которая возвращает 1, -1 или 0 в зависимости от знака числа, переданного ей в аргументе.

    Если мы возьмем результат этой функции от ранжируемого значения и умножим его на посчитанный ранг, то для положительных чисел ранг останется неизменным (мы же просто умножаем его на 1). А вот для отрицательных значений ранг поменяет свой знак.

    Что это значит с точки зрения математики?

    Изначально самое большое отрицательное число (-2) имеет самый большой ранг (4). Самое маленькое отрицательное число (-16) имеет самый маленький ранг (1)

    После умножения ранга на сигнум самый большой ранг отрицательного числа (4) стал самым маленьким среди рангов отрицательных чисел (-4), а самый маленький ранг отрицательного числа (1) стал самым большим рангом среди рангов отрицательных числа (-1).

    То есть, порядок ранга отрицательного числа «перевернулся», что мы и хотели получить.

    Осталось только подправить нашу меру ранга:

    CustomOrder =
    IF (
    ISINSCOPE ( ‘Margin Details'[SubArticle] ) ;
    SIGN ( [Amount] )
    * RANKX ( ALLSELECTED ( ‘Margin Details'[SubArticle] ) ; [Amount]; [Amount]; ASC )
    )

    И поместить эту меру в подсказки каскадной диаграммы,

    Источник: excel-inside.pro