Расчет среднего значения в Excel PowerPivot

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

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

Скажем так, структура данных выглядит следующим образом:

SaleID      SaleLocation        SaleType        SalesRep        SaleDate        WorkGroup       SalesGP
1           Retail1             Car             John A          01/01/2014      Sales           $301
2           HQ                  Bike            John A          01/01/2014      Sales           $200
3           Retail1             Car             Sam L           02/01/2014      Sales           $1300
4           Retail2             Plane           Sam L           02/01/2014      Sales           $72
5           Retail2             Plane           Vince T         03/01/2014      Admin           $55
6           Retail2             Bike            John A          04/01/2014      Sales           $39
7           HQ                  Car             Vince T         05/01/2014      Admin           $2154
....etc

В модели данных Excel я добавил вычисляемые поля (которые используют таблицу поиска) для даты продажи, чтобы продажи можно было отображать по месяцам или годам (например, = ГОД ([SaleDate]) и = МЕСЯЦ ([SaleDate]). )

В качестве примера предположим, что я хочу построить чей-то GP (валовую прибыль) за период времени:

введите описание изображения здесь

У меня такой вопрос ...

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

В приведенном выше примере в сводной диаграмме я хочу построить «средний» ряд, который отображает средний GP по месяцам для всех SalesRep, которые находятся в той же рабочей группе, что и John A («Sales»).

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


excel powerpivot dax excel-formula data-modeling
person Zam    schedule 24.02.2014    source источник

Ответы (1)


arrow_upward
8
arrow_downward

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

Позвольте мне провести вас через это:

Я использовал вашу таблицу данных, а затем добавил ее в свой PowerPivot (Excel 2013). Затем я создал эти рассчитанные меры:

1. Средний объем продаж:

=AVERAGE(SalesData[SalesGP])

2. Среднее значение продаж ВСЕ - вычисляет среднее значение для ВСЕХ строк таблицы и будет использоваться в других расчетах. Обратите внимание, что я использовал первое вычисляемое поле в качестве первого параметра, чтобы сделать это гибким:

=CALCULATE([Sales Amount Average],ALL(SalesData))

3. Сравнение продаж со средним значением. Я не был уверен, какова ваша цель, но я сделал ее немного сложнее, так как я хотел отображать производительность в процентах:

=IF([Sales Amount Average] > 0, [Sales Amount Average] / [Sales Average ALL] -1)

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

Чтобы упростить понимание, я решил использовать гистограммы в условном форматировании вместо отдельной сводной диаграммы - я считаю, что она делает именно то, что вам нужно:

введите описание изображения здесь

На картинке выше первая таблица представляет ваши данные. Вторая таблица является реальной таблицей powerpivot и показывает то, что я описал.

Надеюсь это поможет!

ИЗМЕНИТЬ

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

=IF(HASONEVALUE(SalesData[SalesRep]),
    IF([Sales Amount Average] > 0, 
        [Sales Amount Average] / [Sales Average ALL] -1),
    BLANK()
)

РЕДАКТИРОВАТЬ - ДОБАВЛЕННОЕ СРЕДНЕЕ СРАВНЕНИЕ ДЛЯ РАБОЧИХ ГРУПП

Чтобы рассчитать производительность каждой рабочей группы, а не ВСЕХ торговых представителей, добавьте эти два показателя:

4. Среднее количество продаж на рабочую группу

=CALCULATE(AVERAGE(SalesData[SalesGP]),ALL(SalesData[SalesRep]))

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

5. Разница в продажах в среднем на рабочую группу

=IF(HASONEVALUE(SalesData[SalesRep]),IF([Sales Amount Average] > 0, [Sales Amount Average] - [Sales Average per Workgroup]),BLANK())

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

введите описание изображения здесь

Я загрузил исходный файл в свою общедоступную папку Dropbox.

person Petr Havlik    schedule 09.03.2014
comment
Привет, Петр, это очень полезно, поэтому спасибо за ваше время и усилия. Одна вещь, которую вы упустили, - это тот факт, что я пытаюсь вычислить среднее значение для всех сравнительных торговых представителей в одной и той же рабочей группе. Следуя приведенному выше примеру, мы измеряли Джона А, который входит в рабочую группу по продажам (столбец F). Поэтому, сравнивая его среднее значение, я хочу сравнить его только с другими представителями, которые также входят в рабочую группу по продажам (т.е. только Сэм Л. ... поскольку Винс входит в рабочую группу администратора). - person Zam; 14.03.2014
comment
Зам, я обновил свой ответ - теперь он должен отвечать на все ваши вопросы. - person Petr Havlik; 14.03.2014