Удаление пустых строк — дело простое, т.к. есть команда в интерфейсе.

Удаление пустых столбцов — работа на 10 секунд.

• Удаляем пустые строки — на вкладке Главная страница, в группе Сократить строки, раскрыть кнопку Удалить строки и выбрать Удалить пустые строки

Удаление пустых столбцов происходит в три действия:

• На вкладке Преобразование в группе Таблица выбрать Транспонировать

• На вкладке Главная страница, в группе Сократить строки, раскрыть кнопку Удалить строки и выбрать Удалить пустые строки

• Транспонировать таблицу обратно — на вкладке Преобразование в группе Таблица выбрать Транспонировать

Вот и всё 🙂

Конечно, можно ранжировать данные уже непосредственно в сводной таблице на листе Excel, однако, это не логично. Если мы работаем с моделью Power Pivot, то и все вычисления должны быть подготовлены в ней, чтобы в дальнейшем пользователь мог просто выбирать меру в отчет и не заниматься дополнительными преобразованиями.

Для ранжирования данных необходимо использовать функцию на языке DAX — RANKX:

RANKX(Table;Expression;[Value];[Order];[Ties]),

• Order – порядок ранжирования:
  ASC – ранжирование по возрастанию
  DESC – по убыванию

• Ties – способ определения ранга; для одинаковых значений будет определен одинаковый ранг, а следующим присвоится:
  Skip – следующее значение ранга + количество предыдущих одинаковых значений
  Dense – следующее значение ранга

Ранжируем товары по их суммам продаж.

Создадим меру Продажи,р:=
SUMX(‘БытоваяТехника’;
‘БытоваяТехника'[Количество, шт]*’БытоваяТехника'[Цена за шт, р])

На базе созданной меры Продажи, р для всех наименований из таблицы БытоваяТехника, определим рейтинг:
Рейтинг:=RANKX(ALL(‘БытоваяТехника'[Наименование]);[Продажи,р])

Создав отчет сводной таблицы и добавив созданные меры, получим такой результат:

Как видно, Общий итог самый молодец :).

Починим формулу, добавив логическую функцию IF и функцию фильтрации HASONEVALUE.

• HASONEVALUE(ColumnName) — позволяет определить, вычисляется ли выражение в контексте одного значения для ColumnName. Если ColumnName был отфильтрован до одного значения, то TRUE, иначе — FALSE.

Рейтинг:=IF(
HASONEVALUE(‘БытоваяТехника'[Наименование]);
RANKX(ALL(‘БытоваяТехника'[Наименование]);[Продажи,р]))

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

Всё, что может быть подсчитано, должно быть рассчитано! Работая в Excel, можно вспомнить о дополнительных вычислений в сводных таблицах. Но! В каждом отчете возникает необходимость в добавлении поля, выбор вычисления, изменении имени поля. А при создании вычисляемых полей — мер — в Power Pivot, значительно сокращается количество действий. Ведь подобную настройку мы делаем при создании меры, т.е. один раз!

Формула выглядит так:
Доля продажи, %:=SUMX(‘БытоваяТехника’;
‘БытоваяТехника'[Цена за шт, р]*‘БытоваяТехника'[Количество, шт])/
SUMX(ALL(‘БытоваяТехника’);
‘БытоваяТехника'[Цена за шт, р]*’БытоваяТехника'[Количество, шт])

• Функция SUMX(Table;Expression) — позволяет без создания вычисляемых столбцов, сразу задать расчетное выражение по исходным столбцам. Когда мера добавляется в отчет, то значения рассчитываются по значениям категорий. Например, наименование или производитель.

• Функция ALL игнорирует все возможные фильтры и вычисляет итоговое значение по всем данным.

Затем следует применить к созданной мере процентный формат. Остается добавить вычисляемое поле в нужный отчёт. Готово!

Расчет доли продажи по наименованиям:

Расчет доли продажи по производителям: