Общий процент маржи в Excel: почему нельзя усреднять проценты строк

По одному товару маржа 50%, по другому 10%. Простое среднее этих процентов равно 30%. Но если первый товар продан на 100 рублей, а второй на 900, общий процент маржи составит 14%. Итог надо считать из общих денежных сумм: прибыль и выручка каждой строки входят в расчёт со своим фактическим размером.

Разделите валовую прибыль и её долю

Валовая прибыль строки равна выручке минус себестоимость. Процент маржи — валовая прибыль, делённая на выручку. Если в данных указаны цена и количество, сначала получите денежные суммы строки по согласованному правилу. Скидки, возвраты и налоги должны учитываться одинаково во всех частях отчёта.

Не путайте маржу с наценкой: у наценки в знаменателе себестоимость. Для выручки 100 и себестоимости 60 маржа 40%, а наценка около 66,67%. Название столбца должно объяснять, какая именно доля показана.

Считайте итог через суммы

Для двух товаров прибыль составляет 50 и 90, суммарная выручка 1000. Поэтому общий процент равен 140/1000=14%. Среднее процентов строк даёт 30% и приравнивает товар с выручкой 100 к товару с выручкой 900.

В Power Pivot создайте меры выручки, себестоимости и валовой прибыли. Затем разделите меру прибыли на меру выручки через DIVIDE. Пример использует готовые денежные столбцы Revenue и Cost в таблице Sales. Разделители аргументов DAX могут зависеть от настроек редактора.

Revenue := SUM(Sales[Revenue])
Cost := SUM(Sales[Cost])
GrossProfit := [Revenue] - [Cost]
MarginPct := DIVIDE([GrossProfit], [Revenue])

Проверьте итоги под фильтрами

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

Для нулевой выручки DIVIDE без альтернативного результата вернёт BLANK. Обсудите, как это показывать пользователю: отсутствие основания для процента отличается от нулевой маржи при ненулевой выручке.

Разберите возвраты и неизвестную себестоимость

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

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

Для приёмки возьмите две строки с резко разной выручкой, проверьте отдельные проценты и общий 14%. Затем выберите один товар срезом: мера должна дать его 50% или 10%. Добавьте месяц и убедитесь, что итог по периоду использует суммы именно выбранных строк.

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

Соберите обновляемый отчёт на практике

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

Откройте программу курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».

Начните с пробного урока о рабочей книге и первом запросе Power Query.

Для следующего этапа: объединение файлов из папки и обновление отчёта.