Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
DAX использование столбца из виртуальной таблицы
 
#1
01.06.2020 19:27:34
Доброго времени суток!
Прошу помочь со следующим вопросом:
1.Имеется модель данных, состоящая из измерений Product,Calendar и фактов Sales, Ostatki
2. В виртуальной таблице я думаю что рассчитываю список всех товаров, к которым по условию добавляю столбец если скидка >30% или <0% то 1, в противном случае 0
3.В моем понимании эта таблица для каждого месяца своя
4.Остатки умножаю на столбец с 1 или 0 и получаю только те остатки, которые продавались со скидкой.
Вопрос, не могу перемножить столбец из виртуального столбца [t2] с остатками
На фото сводная таблица
Код
ОстатокЕдСоСкидкой>30% =
VAR _t2 =
//Создаю виртуальную таблицу, которая должна в сводной в контексте фильтра по месяцам создавать таблицу с условием скидки
    ADDCOLUMNS (
        VALUES ( 'Product'[SKU] ),
        "t2", IF (
            (
                1
                    - DIVIDE (
                        CALCULATE ( SUM ( Sales[Выручка] ) ),
                        (
                            CALCULATE (
                                SUMX ( 'Sales', 'Sales'[Ед.Продано] * RELATED ( Product[ПервоначальнаяЦена] ) )
                            )
                        )
                    )
            ) > 0.3
                || (
                    1
                        - DIVIDE (
                            CALCULATE ( SUM ( Sales[Выручка] ) ),
                            (
                                CALCULATE (
                                    SUMX ( 'Sales', 'Sales'[Ед.Продано] * RELATED ( Product[ПервоначальнаяЦена] ) )
                                )
                            )
                        )
                ) < 0,
            1,
            0
        )
    )
// не могу умножить остатки на [t2]
RETURN
    CALCULATE (
        SUMX ( 'Ostatki', 'Ostatki'[Остаток.Ед] * [t2] ),
        LASTNONBLANK (
            'Календарь'[Date],
            CALCULATE ( COUNTROWS ( 'Ostatki' ), ALL ( Product ) )
        )
    )
Изменено: Lari - 02.06.2020 11:58:28
 
 
 
#2
02.06.2020 11:49:40
Мне кажется странным, что Вы в переменную поместили целую таблицу, а потом ссылаетесь на ее столбец целиком никак не агрегируя его значения.
Я могу и ошибаться, но мне кажется, что причина может быть в этом.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#3
02.06.2020 11:57:15
Lari, а как умножить на [t2] если это столбец виртуальной таблицы, а SUMX у вас вычисляет в разрезе строкового контекста таблицы 'Ostatki'. В этом контексте нет значений вашей виртуально таблицы, а значит умножение происходит на BLANK(). Я это так вижу.
Вы ж тут не впервые и опыт работы с DAX у вас имеется. Приложите простенький пример вашей модельки и покажите что нужно получить в сводной.
Для оптимизации Вот эту штуку лучше засунуть в переменную, зачем вы ее два раза считаете? И быстрее и код читабельнее будет.
Код
            (
                1
                    - DIVIDE (
                        CALCULATE ( SUM ( Sales[Выручка] ) ),
                        (
                            CALCULATE (
                                SUMX ( 'Sales', 'Sales'[Ед.Продано] * RELATED ( Product[ПервоначальнаяЦена] ) )
                            )
                        )
                    )
            )
Изменено: PooHkrd - 02.06.2020 12:02:25
Вот горшок пустой, он предмет простой...
 
 
 
#4
04.06.2020 12:35:54
Доброго времени суток!
Dyroff, к сожалению проблема в этом тоже заключается что таблица виртуальная, и related не работает .
PooHkrd, я читал  про snapshot или transition matrix, но ведь в Power Pivot нельзя создать таблицу как в Power BI, поэтому единственный способ это создавать виртуальную таблицу.
А как умножить на значения из виртуальной таблицы, я не знаю.
По поводу примера, файл примеров Contoso от итальянце весит 27мб с продажами и остатками и товарами и датами.
Код
SUMX ( 'Ostatki', 'Ostatki'[Остаток.Ед] * related('_t2'[t2] )) - не работает
Код
ОстатокЕдСоСкидкой>30%:=
VAR _F1 =
    (
        1
            - DIVIDE (
                CALCULATE ( SUM ( Sales[Выручка] ) ),
                (
                    CALCULATE (
                        SUMX ( 'Sales', 'Sales'[Ед.Продано] * RELATED ( Product[ПервоначальнаяЦена] ) )
                    )
                )
            )
    )
VAR _t2 =
    //Создаю виртуальную таблицу, которая должна в сводной в контексте фильтра по месяцам создавать таблицу с условием скидки
    ADDCOLUMNS (
        VALUES ( 'Product'[SKU] ),
        "t2", IF ( _F1 > 0.3 || _F1 < 0, 1, 0 )
    )
 // не могу умножить остатки на [t2]
RETURN
    CALCULATE (
        SUMX ( 'Ostatki', 'Ostatki'[Остаток.Ед] * related('_t2'[t2]) ),
        LASTNONBLANK (
            'Календарь'[Date],
            CALCULATE ( COUNTROWS ( 'Ostatki' ), ALL ( Product ) )
        )
    )
Изменено: Lari - 04.06.2020 12:36:45
 
 
 
#5
04.06.2020 12:43:34
Цитата
Lari написал:
в Power Pivot нельзя создать таблицу как в Power BI
Это не совсем соответствует действительности, вот прям формулами DAX, да, нельзя, но другими способами можно. Можно и Power Query задействовать и SQL. Голь на выдумку хитра, главное понять задачу.
Зачем мне ваш Contoso (кстати, не помню чтобы в ней была таблица с названием 'Ostatki')? Сделайте свой пример чтобы уложиться в 300 кб и объясните чего вам хочется.
Вот горшок пустой, он предмет простой...
 
 
 
#6
04.06.2020 13:12:58
PooHkrd, в контосо нет таблицы Ostatki а у меня на работе есть, я не стал остатки переделывать поскольку и так понятно что это , а остальные столбцы я переделал под таблицы контосо.
Для моей задачи выходить за рамки решения этого вопроса силами DAX будет избыточным. Если это не решается мерой, значит не решается.
Необходимость примера абсолютно справедлива, но в данном случае, придумывать продажи, остатки, цены, скидки и все по датам, тоже избыточно.
Не сочтите за наглость.
Если вдруг вы приобретали книгу Power BI о которой Николай недавно писал в блоге, то модель соответствует примеру F06xx01.
Мне нужен остаток тех товаров, которые не продавались в течение месяца или продавались со скидкой >30%.
Там Net Price в таблице Sales  как я понял цена продажи, а первоначальная цена это Unit Price в таблице Product.
 
 
 
#7
04.06.2020 13:27:48
Цитата
Lari написал:
Мне нужен остаток тех товаров, которые не продавались в течение месяца или продавались со скидкой >30%
Подобная задача решается авторами той самой книги здесь. Там с примерами и все такое. Только вас интересуют не пропавшие покупатели, а пропавшие из продаж позиции, что в принципе одно и то же. и по аналогии же можно будет решить задачу и про скидку.
Вот горшок пустой, он предмет простой...
 
 
 
#8
04.06.2020 13:42:31
PooHkrd, большое спасибо, буду разбирать, 4-й уровень сложности, интересно))
Я читал в правилах форума что нельзя параллелить вопросы на разных форумах, но буквально только что написал в PBI в телеграме и там подсказали такое решение, еще тоже не совсем понимаю как работает, но на данных сработало.
Код
=
CALCULATE (
    SUMX (
        'Ostatki',
        'Ostatki'[Остаток.Ед]
            *
            VAR _F1 =
                (
                    1
                        - DIVIDE (
                            CALCULATE ( SUM ( Sales[Выручка] ) ),
                            (
                                CALCULATE (
                                    SUMX ( 'Sales', 'Sales'[Ед.Продано] * RELATED ( Product[ПервоначальнаяЦена] ) )
                                )
                            )
                        )
                )
            RETURN
                IF ( _F1 > 0.3 || _F1 < 0, 1, 0 )
    ),
    LASTNONBLANK (
        'Календарь'[Date],
        CALCULATE ( COUNTROWS ( 'Ostatki' ), ALL ( Product ) )
    )
)
 
 
 
#9
04.06.2020 15:58:47
К сожалению, приведенная выше формула не выдает желаемый результат.
Получается нужна таблица остатков на последнюю дату выбранного периода, таблица SKU со скидками за выбранный период и сумма только тех остатков, скидка по которым соответствует условию. Буду разбирать ссылку из #7.
Изменено: Lari - 04.06.2020 15:59:36
 
 
 
#10
04.06.2020 16:35:53
Кажется такой код работает
Код
=
SUMX (
    FILTER (
        VALUES ( 'Product'[SKU] );
        VAR _F1 =
            (
                1
                    - DIVIDE (
                        CALCULATE ( SUM ( 'Sales'[Выручка] ) );
                        (
                            CALCULATE (
                                SUMX ( 'Sales'; 'Sales'[Ед.Продано] * RELATED ( 'Product'[ПервоначальнаяЦена] ) )
                            )
                        )
                    )
            )
        RETURN
            _F1 > 0,3
            || _F1 < 0
    );
    [OnHandQuantity]
)
 
 
 
Страницы: 1
Наверх