Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
DAX. Фильтр для исключения при суммировании дубликатов по условию.
 
#1
18.09.2021 20:45:48

Всем добра и благополучия!

Собственных познаний в DAX не хватает, поэтому прошу «помощи зала», советом куда копать, или примером реализации. В прикрепленном файле таблица, она же модель данных. В каждой строке все ячейки, за исключением «Дата изменения» и «Сумма», являются уникальными. Пытаюсь придумать меру, которая суммировала бы столбец «Сумма», но при этом для дублирующихся строк, брала строку с максимальным значением в столбце «Дата изменения». Долго безуспешно кувыркался, поэтому свои меры в пример не вставил.

За название темы не бейте, точнее придумать пока не получилось
 
 
 
#2
18.09.2021 22:17:08
Доброе время суток
Цитата
ТипТоп написал:
брала строку с максимальным значением в столбце «Дата изменения»
Тогда почему для Москва, ООО Ромашка, 2019 не использован в результате вывод 15.09.2020 - 6 800,00? Для Москва, ООО Груша, 2019 - 15.08.2020 950,00?
Почему раскраска в "Нужный результат" по названиям ООО не совпадает с раскраской в таблице источнике?
 
 
 
#3
18.09.2021 22:30:28
Андрей VG, Цветом в таблице подсвечены "дублирующиеся" строки, в результате перепутал местами цвета, сори.
Цитата
Андрей VG написал:
ООО Ромашка, 2019 не использован в результате вывод 15.09.2020 - 6 800,00? Для Москва, ООО Груша, 2019 - 15.08.2020 950,00?
Почему не использован? Именно эти значения и должны использоваться в суммировании.
 
 
 
#4
18.09.2021 22:50:31
Цитата
ТипТоп написал:
Почему не использован?
Посмотрите то, что вы приложили. Объясните, тогда как вы это получили
 
 
 
#5
18.09.2021 23:07:58
Красным    Москва- ООО Груша- 2019 год
Зеленым    Москва- ООО Ромашка -2019 год
 
 
 
#6
19.09.2021 07:03:28
я знаю ,что тут есть умные люди ,которые могут предложить Вам вариант лучше чем мой.
на что я очень хочу посмотреть (буду ждать их варианты), но пока вот мое старание:

Код
=VAR tabM =
    ADDCOLUMNS (
        VALUES ( 'Таблица2' );
        "Rang";
            VAR t1 = 'Таблица2'[Регион]
            VAR t2 = 'Таблица2'[Компания]
            VAR t3 = 'Таблица2'[Отчетный период]
            VAR t4 = 'Таблица2'[Дата отчета]
            VAR t5 = 'Таблица2'[Продукция]
            RETURN
                RANKX (
                    FILTER (
                        'Таблица2';
                        t1 = 'Таблица2'[Регион]
                            && t2 = 'Таблица2'[Компания]
                            && t3 = 'Таблица2'[Отчетный период]
                            && t4 = 'Таблица2'[Дата отчета]
                            && t5 = 'Таблица2'[Продукция]
                    );
                    'Таблица2'[Дата  изменения];
                    ;
                    DESC
                )
    )
RETURN
    CALCULATE ( SUM ( 'Таблица2'[Сумма] ); FILTER ( tabM; [Rang] = 1 ) )
т
Изменено: azma - 19.09.2021 09:13:17
 
 
 
#7
19.09.2021 10:10:56
azma, снимаю шляпу и рукоплещу стоя! :)
И, да, упростить выражение можно, особенно учитывая требуемую структуру вывода.
Код
=SUMX(GROUPBY('Таблица2'; 'Таблица2'[Дата отчета]; 'Таблица2'[Продукция]);
    var latestByReportDateAndProduct = CALCULATETABLE(TOPN(1; 'Таблица2'; 'Таблица2'[Дата  изменения]; DESC))
    Return CALCULATE(SUM('Таблица2'[Сумма]); latestByReportDateAndProduct)
)
 
 
 
#8
19.09.2021 10:24:49
azma, Андрей VG, Огромнейшее спасибо!
 
 
 
#9
19.09.2021 10:47:53
Цитата
Андрей VG написал:
упростить выражение можно
Супер, спасибо большое!
 
 
 
#10
19.09.2021 11:02:14
Цитата
ТипТоп написал:
придумать меру, которая суммировала бы столбец «Сумма», но при этом для дублирующихся строк, брала строку с максимальным значением в столбце «Дата изменения»
Можно и Power Query использовать
Если с Dax не на ты :)  
НА МЯСО В ПРОФИЛЕ
 
 
 
#11
19.09.2021 13:44:14
Андрей VG, позвольте поинтересоваться, почему используете GROUPBY, а не SUMMARIZE? Вроде, SUMMARIZE более производительная функция, а GROUPBY советуют использовать для вложенных группировок. P.S. Ни в коем случае не критикую, спрашиваю только в образовательных целях;)
Изменено: surkenny - 19.09.2021 14:17:19
 
 
 
#12
19.09.2021 14:46:47
Цитата
surkenny написал: почему используете GROUPBY
Руссо М., Феррари А. говорят лучше не использовать SUMMARIZE (так написали в книге "Шаблоны DAX")
хотя они сами в своих примерах используют её :)
https://www.sqlbi.com/blog/marco/2014/11/18/don-t-use-summarize-to-sum-your-data-or-just-be-careful-...
https://www.sqlbi.com/blog/marco/2016/02/15/groupby-vs-summarize-in-dax-powerbi-powerpivot/
Изменено: vikttur - 19.09.2021 16:36:37
 
 
 
#13
19.09.2021 16:02:34
Цитата
azma написал:
лучше не использовать SUMMARIZE
они говорят не использовать SUMMARIZE для столбцов с агрегированными значениями (хотя это есть в функции), а использовать конструкцию ADDCOLUMNS(SUMMARIZE()). А во второй ссылке речь как раз о вложенной группировке (когда группируем сгруппированное) и конструкция ADDCOLUMNS(SUMMARIZE(ADDCOLUMNS(SUMMARIZE()))) выдаст ошибку.
Изменено: surkenny - 19.09.2021 16:06:35
 
 
 
#14
19.09.2021 17:03:53
Цитата
surkenny написал:
Вроде, SUMMARIZE более производительная функция
Коллега, а можно показать - на основании чего сделано такое заключение? Сопоставил GROUPBY и SUMMARIZE для получения строкового контекста
Код
EVALUATE
ADDCOLUMNS(SUMMARIZE('Query1', Query1[DateKey], Query1[StoreKey]), "foo", 1)

Исходник - типовая база ContosoRetailDW с таблицей dbo.FactOnlineSales c более чем 12,6 миллиона строк. Создаётся таблица на 3288 строк. Типовые результаты выполнения в DAX Studio - разница в пределах погрешности по нескольким стартам. :)
Я ожидал критику, но не в этой части. Займусь самокритикой
1. Формально не определено что набор полей: Регион; Компания; Отчетный период; Дата отчета; Дата  изменения; Продукция - это первичный ключ. Если нет, и существует несколько записей, отличающихся только значением поля Сумма, то в решении azma будет один результат, у меня другой результат - значение поля Сумма первой попавшейся записи, удовлетворяющей условию отбора. Естественно, можно перепилить и на сумму, получив максимальную дату тем же путём, а уж её использовать в фильтре CALCULATE
2. Если возвращается кодом
Код
var latestByReportDateAndProduct = CALCULATETABLE(TOPN(1; 'Таблица2'; 'Таблица2'[Дата  изменения]; DESC))

всегда одна строка, то тогда зачем ею фильтровать входные данные?!
То есть вместо
Код
Return CALCULATE(SUM('Таблица2'[Сумма]); latestByReportDateAndProduct)

использовать обработку уже полученной виртуальной таблицы (собственно извлечение данных поля записи)
Код
Return SUMX(latestByReportDateAndProduct; 'Таблица2'[Сумма])

:)
 
 
 
#15
19.09.2021 18:47:10
Цитата
Андрей VG написал:
Я ожидал критику, но не в этой части.
Ни в коем случае! Я даже написал, что не критикую;)
Я сам всегда использую только SUMMARIZE для подобного, поэтому и интересуюсь. Не попадалась мне пока необходимость вложенной группировки.
В книге Руссо и Феррари:
«В  плане  производительности  функция  GROUPBY  может  проседать  применительно  к  большим  наборам  данных  –  начиная  от  нескольких  десятков  тысяч записей  и  выше. Фактически функция приступает к осуществлению группировки  только  после  завершения  процесса  материализации  таблицы,  а  значит,  она не  слишком  применима  к  большим  наборам  данных.  Кроме  того,  в  большинстве  случаев  запросы  легче  выразить  через  сочетание  функций  ADDCOLUMNS и  SUMMARIZE.»
И по второй ссылке azma:
«Moreover, from a performance point of view, GROUPBY does not take advantage of VertiPaq and materializes the rows to be grouped by the formula engine. Thus, GROUPBY should be used only to group the result of another calculation already materialized (such as the result of a SUMMARIZE function).»
 
 
 
#16
19.09.2021 20:15:09
Цитата
surkenny написал:
в  большинстве  случаев  запросы  легче  выразить  через  сочетание  функций  ADDCOLUMNS и  SUMMARIZE
Э, коллега. Тут суть несколько дальше. Не рекомендуется выполнять вычисления непосредственно в SUMMARIZE и GROUPBY. А вот если использовать для формирования строкового контеста, используемого в CALCULATE - и что сделано у меня (собственно, если почитать относительно свежие статьи итальянцев, то вы это увидите - я не его изобретатель :) ), то это не ведёт к деградации производительности.
Например, среднее средних.
Чем это
Код
=Var stats = ADDCOLUMNS(SUMMARIZE('Query1'; 'Query1'[DateKey]; 'Query1'[StoreKey]);
    "ds_avg"; CALCULATE(AVERAGE('Query1'[TotalCost]))
)
Return AVERAGEX(stats; [ds_avg])

будет быстрее, чем
Код
=AVERAGEX(SUMMARIZE('Query1'; 'Query1'[DateKey]; 'Query1'[StoreKey]);
    CALCULATE(AVERAGE('Query1'[TotalCost]))
)

Подход в использовании SUMMARIZE и GROUPBY ровно тот же. И производительность у них тут сопоставима. Собственно, об этом и написано следующее
Цитата
Цитата
surkenny написал:
Thus,  GROUPBY  should be used only to group the result of another calculation
Изменено: Андрей VG - 19.09.2021 20:15:58
 
 
 
Страницы: 1
Наверх