Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Pivot: корректный подсчет Grand Total
 
#1
12.12.2018 10:25:16
Добрый день!

Создаю в Power Pivot меру, которая должна для каждого месяца брать значение в зависимости от статуса месяца:
1. Если месяц закрыт, то берется значение факта (Fact)
2. Если месяц не закрыт, то берется значение плана (Target)
3. Если сценарий не фактический (Fact Or No = 0), то берется полностью плановое значение (Plan)

Проблема в том, что по месяцам значения считаются корректно, но Grand Total для колонки со скользящим сценарием (Fact 2018) выводит не сумму этих значений, а расчетное значение, соответствующее сумме факта (без учета статуса месяца).

Как это можно исправить?
 
 
 
#2
12.12.2018 13:39:22
Доброе время суток
Как-то странно у вас представлены данные. Таблица календаря 'календарь'[Open (1)/Close (0)], 2017 год значение 1 - следовательно месяц открыт, тогда для 2017 по строкам месяцам считаем данные столбца Target (FC MIX), но для этого года нет значений, а в вашей сводной вы чего-то насчитали, это правильно?
Далее. Пусть это ошибка 0 с 1 перепутали в 'календарь'[Open (1)/Close (0)]. Тогда, для 2018 года для ноября и декабря в календаре 0, то есть месяц не закрыт. Считаем Target (FC MIX), но для этих месяцев нет данных - должно быть пусто. У вас же в сводной есть значения.
В общем пока такая мера, опуская неописанное в определении использование Calculate, но строго по 1, 2, 3 с учётом того, что по значению 'календарь'[Open (1)/Close (0)] месяц: 1 закрыт, 0 открыт.
Код
SUMX('SI_CENTRAL';
    IF('SI_CENTRAL'[Fact Or No] = 1;
        IF(RELATED('календарь'[Open (1)/Close (0)]) = 1;
            [Sum of Fact SI PCS/KG];
            [Sum of Target (FC MIX)]
        ) ;
        [Sum of PLAN SI]
    )
)

P. S. Не стесняйтесь форматировать код для смысловой читабельности - большинство просто пройдут мимо, так как нет желания разбираться в "каше".
Изменено: Андрей VG - 12.12.2018 13:40:18
 
 
 
#3
12.12.2018 18:36:40
Огромное спасибо за Ваш ответ!
Вы совершенно правы:
1. в названии колонки Open (1)/Close (0) была путаница, должно быть Open (0)/Close (1),
2. некорректно в исходной таблице заполнена колонка Target (FC MIX): она должна заполняться значениями сценария FC0 для первого квартала, FC1 для второго квартала, FC2 - для третьего и FC3 - для четвертого.
Эти ошибки в файле поправила.
А дальше смысл такой:
1. Если сценарий не Fact, то берется значение PLAN SI (здесь и Ваш, и мой показатель отрабатывают без ошибок)
2. Если сценарий Fact и месяц закрыт (Open (0)/Close (1) = 1), то должно браться значение Fact SI PCS/KG (здесь также и Ваш, и мой показатель отрабатывают без ошибок)
3. Если сценарий Fact и месяц открыт (Open (0)/Close (1) = 0), то должно браться значение Target (FC MIX) (которое в зависимости от периода равно PLAN SI по определенному сценарию). Вот здесь мой показатель по месяцам отрабатывает корректно, но неправильно считает Grand Total. Ваш показатель не выводит значения Target (FC MIX) в закрытых месяцах.

Очень надеюсь на Вашу помощь.

Прилагаю файл со сравнением двух показателей.

Большое спасибо за совет с форматированием кода, пока учусь, советы будут очень полезны.
 
 
 
#4
13.12.2018 18:49:38
Логика не до конца понятна. Вот у вас 2018 год, сентябрь, Forecast Chart = "Fact - 2018"
Для этого набора данных автоматически:
[Open (0)/Close (1)] = 0 // не закрыт
[Fact Or No] = 1 // факт
(т.е. это фактический сценарий в незакрытом месяце).
Что должно находиться для этого месяца и этого Forecast Chart? Вы хотите выводить там target? Но для этого Forecast Chart нет данных о Target. их нужно выводить принудительно, не обращая внимания на выбранный chart?
F1 творит чудеса
 
 
 
#5
13.12.2018 18:53:54
Ну попробуйте:
Код
=
SUMX (
    SUMMARIZE (
        'SI_CENTRAL';
        'календарь'[Month (Month)];
        'календарь'[Open (0)/Close (1)];
        'SI_CENTRAL'[Fact Or No]
    );
    IF (
        'SI_CENTRAL'[Fact Or No] = 1;
        //если факт
        IF (
            'календарь'[Open (0)/Close (1)] = 0;
            // если месяц не закрыт, то берем target и плевать на остальное
            CALCULATE (
                [Sum TARGET];
                ALL ( SI_CENTRAL[Forecast Chart] );
                ALL ( SI_CENTRAL[Fact Or No] )
            );
            // если месяц закрыт
            [Sum FACT]
        );
        // если не факт
        [Sum PLAN SI]
    )
)

17 и 18 строки в коде описывают, что мы игнорируем при условии  'SI_CENTRAL'[Fact Or No] = 1 и 'календарь'[Open (0)/Close (1)] = 0
F1 творит чудеса
 
 
 
#6
17.12.2018 12:37:47
Огромное спасибо за предложенное решение, работает!

В идеале, для факта по незакрытым месяцам нужно брать даже не Target, а PLAN SI по тому сценарию, который актуален на первый незакрытый месяц.
Актуальность определяется следующим образом:
январь, февраль, март - актуальный сценарий FC0
апрель, май, июнь - актуальный сценарий FC1
июль, август, сентябрь- актуальный сценарий FC2
октябрь, ноябрь, декабрь- актуальный сценарий FC3

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

Код
=SUMX (
    SUMMARIZE (
        'SI_CENTRAL';
        'календарь'[Month (Month)];
        'Календарь'[Open (1)/Close (0)];
        'SI_CENTRAL'[Fact Or No]
    );
    IF (
        'SI_CENTRAL'[Fact Or No] = 1;
        //если факт
        IF (
            'Календарь'[Open (1)/Close (0)] = 0;
            // если месяц не закрыт, то берем target и плевать на остальное
            CALCULATE (
                [Sum of PLAN SI];
                FILTER(ALL ( SI_CENTRAL[Forecast Chart] );[Forecast Chart]="FC3 - 2018");
                ALL ( SI_CENTRAL[Fact Or No] )
            );
            // если месяц закрыт
            [Sum of Fact SI PCS/KG]
        );
        // если не факт
        [Sum of PLAN SI]
    )
)


Может быть подскажете, как вычислить первый незакрытый месяц и прописать в зависимости от его значения нужное значение сценария?
Изменено: SashaSpb - 17.12.2018 12:40:15
 
 
 
#7
17.12.2018 12:41:56
SashaSpb, Вы видите, как оформлен код у Максима? Вот и Вы делайте аналогично: ищите такую кнопку (см. скрин) и исправьте своё сообщение.
 
 
 
#8
17.12.2018 17:03:37
Цитата
SashaSpb написал:
как вычислить первый незакрытый месяц
в общем случае сценарий первого незакрытого месяца наверное можно определить так:
не проверял, но попробуйте
Изменено: Максим Зеленский - 17.12.2018 17:03:49
F1 творит чудеса
 
 
 
#9
05.01.2019 20:14:33
Цитата
Максим Зеленский написал:
в общем случае сценарий первого незакрытого месяца наверное можно определить так:

Огромное спасибо, совет помог, нужный результат достигнут! Пришлось немного подработать формулы для вычисления нужного сценария под свою более сложную ситуацию, чем в примере, но в целом предложенная логика здорово помогла!
 
 
 
Страницы: 1
Наверх