Страницы: 1
RSS
DAX отмена фильтра для части данных столбца
 
Здравствуйте!

Имеется две таблицы связанных между собой по колонке [№ месяца]. Таблица_данных регулярно расширяется вниз по мере поступления информации.
Моя задача сделать меру сводной таблицы, которая бы считала сумму за каждый год поп полю [Сумма], но при этом не ограничивая помесячную фильтрацию для последнего года. Т.е. при выборе месяца в слайсере Таблица_месяцев должны фильтроваться только месяцы для последнего года в Таблица_данных.



На данный момент я додумался только до следующей меры:

Код
=CALCULATE(
         SUMX(Таблица_данных,Таблица_данных[Сумма]*1000)
          ,ALL(Таблица_месяцев)
        )


Но она как раз таки складывает все суммы по каждому месяцу и для последнего года тоже, а слайсер фильтрует месяцы по всем годам.

Собственно мой вопрос: Как сделать, чтобы при использовании ALL(), либо его замене, Слайсер работал в сводной таблице только для последнего года?
 
Цитата
Barly написал:
только для последнего года?
а какой год считать последним? Если это текущий, то можно так:
Код
=SUMX(FILTER(Таблица_данных;'Таблица_данных'[Год]=Year(NOW()));Таблица_данных[Сумма])
если же отбирать надо последний год, который в принципе есть в таблице, то можно и так:
Код
=SUMX(FILTER(Таблица_данных;'Таблица_данных'[Год]=Max('Таблица_данных'[Год]));Таблица_данных[Сумма])
хотя правильнее вынести Max('Таблица_данных'[Год]) в переменную:
Код
=var lY = Max('Таблица_данных'[Год])
=SUMX(FILTER(Таблица_данных;'Таблица_данных'[Год]=lY);Таблица_данных[Сумма])

P.S. я так понял, что надо отбирать именно по выбранным месяцам, а значит ALL тут лишняя.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
Дмитрий(The_Prist) Щербаков написал:
P.S. я так понял, что надо отбирать именно по выбранным месяцам, а значит ALL тут лишняя.

Дмитрий, спасибо за ответ,

Допустим считаем текущий год (он должен быть максимальным в таблице).

Да, но в представленных примерах, я просто отфильтрую по годам в заданном формулами диапазоне. А как это повлияет на мои месяцы?
Мне ведь нужно, чтобы я мог слайсером выбирать месяцы только в последнем году, а в прошлые годы мера должна выдавать все 12 месяцев.
И это всё в одной сводной таблице...

На рисунке ниже я изобразил сводную таблицу, которая должна получиться из этих данных.
Там где стрелки с 12 - суммы за весь соответствующий год и не меняются;
Там где желтая стрелка - сумма меняется в зависимости от выбранного месяца в слайсере;

 
Доброе время суток.
Цитата
Barly написал:
текущий год (он должен быть максимальным в таблице)
Ну, так и вычисляете максимальный год. Далее, в цикле SUMX по годам определяете если текущий год максимальный, то просто сумма в обёртке CALCULATE, иначе сумма в CALCULATE со сбросом фильтрации месяцев.
 
Цитата
Barly написал:
но в представленных примерах, я просто отфильтрую по годам в заданном формулами диапазоне
что-то не увидел этого на скринах в первом сообщении - они вводят в заблуждение.
Цитата
Barly написал:
На рисунке ниже я изобразил
Вы может файл уже приложите с данными? Зачем Вы нам схемы какие-то выкладываете? Дело, конечно, Ваше, но лично у меня нет желания сидеть и переписывать Ваши данные со скрина, потом угадывать что у Вас там за формулы и что за фильтры, а потом еще по выдуманным мною же данным решение Вам делать.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
Дмитрий(The_Prist) Щербаков написал:
Вы может файл уже приложите с данными?

Да, извиняюсь. Вот файл
 
Цитата
Barly написал:
Вот файл
Вот мера
Код
=Var latestYear = CALCULATE(MAXX('Таблица_данных'; 1 * 'Таблица_данных'[Год]); ALL('Таблица_данных')) & ""
Return SUMX(GROUPBY('Таблица_данных'; 'Таблица_данных'[Год]);
    IF('Таблица_данных'[Год] = latestYear;
        CALCULATE(SUM('Таблица_данных'[Сумма]));
        CALCULATE(SUM('Таблица_данных'[Сумма]); ALL('Таблица_месяцев'))
    )
)
P. S. А какими соображениями вы руководствовались, выбирая тип данных для поля Год - текстовый?
Изменено: Андрей VG - 24.09.2021 09:54:12
 
Цитата
Андрей VG написал:
Вот мера

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

Цитата
Андрей VG написал:
P. S. А какими соображениями вы руководствовались, выбирая тип данных для поля Год - текстовый?

Никакими, просто добавил в пример как есть, упустив этот нюанс из вида.
 
Цитата
Barly написал:
исчезают суммы в 2019 и 2020 годах.
А они, эти суммы, существуют? Если да, то приведите пример, что есть, как должно. У меня ничего по вашим данным не исчезает.
Цитата
Barly написал:
то остальные категории исчезают из сводной таблицы.
Ну, если нужны "пустые категории", то почему не включаете в параметрах сводной "Показывать элементы без данных в строках/столбцах"?
 
Barly написал:
Слайсер работал в сводной таблице только для последнего года?


Если я правильно Вас понял, то попробуйте менять :
Код
SUMX(GROUPBY('Таблица_данных'; 'Таблица_данных'[Год]);    IF('Таблица_данных'[Год] = latestYear;
        CALCULATE(SUM('Таблица_данных'[Сумма]));
        CALCULATE(SUM('Таблица_данных'[Сумма]); ALL('Таблица_месяцев'))
    )
)

на это:
Код
IF(HASONEFILTER('Таблица_данных'[Год] ),IF(MAX('Таблица_данных'[Год] )= latestYear,
 CALCULATE(SUM('Таблица_данных'[Сумма]));         
CALCULATE(SUM('Таблица_данных'[Сумма]); ALL('Таблица_месяцев'))),BLANK())
Изменено: azma - 26.09.2021 21:07:38
 
Цитата
azma написал:
на это:

Огромное спасибо! С этим изменением всё заработало как нужно!
Единственный момент: на больших данных фильтрация тормозит (при кликах по слайсеру тупит)?

Изменено: Barly - 28.09.2021 03:39:20
 
насчет того, что слайсер как будто неактивный, честно не знаю почему так, может нам специалисты скажут почему так.
А для  решения данной проблемы, просто нужно убрать -IF(HASONEFILTER- , и скрыть итоги по строкам поскольку они некорректные

файлы удалены - превышение допустимого размера вложения [МОДЕРАТОР]
Изменено: vikttur - 28.09.2021 11:19:27
 
Цитата
azma написал: насчет того, что слайсер как будто неактивный
Методом тыка решил проблему со слайсером заменив в формуле BLANK() на 0.
Полученная формула работает и я подумал это уже финиш:
Код
=IF(MAX('Таблица_данных'[Год])= latestYear,
   CALCULATE(SUMX(Таблица_данных,Таблица_данных[Сумма])),         
   CALCULATE(SUMX(Таблица_данных,Таблица_данных[Сумма]), ALL('Таблица_месяцев')
))
Но теперь проблема при фильтрации последнего года. Он суммируется как и нужно было в зависимости от выбранного месяца в фильтре,
но суммы в итогах по строкам, как вы и пишете, не равны Итоговой сумме. Причем если выбрать все месяцы, то корректно. А как сделать чтобы были равны?
Изменено: vikttur - 28.09.2021 11:19:48
 
Цитата
Barly написал:
но суммы в итогах по строкам, как вы и пишете, не равны Итоговой сумме. Причем если выбрать все месяцы, то корректно. А как сделать чтобы были равны?
Проблема решилась для фильтрации последнего года добавлением SUMX(GROUPBY(
Код
=Var latestYear = CALCULATE(MAXX('Таблица_данных', 1*'Таблица_данных'[Год]), ALL('Таблица_данных')) 
Return 
SUMX(GROUPBY(Таблица_данных, 'Таблица_данных'[Год]),
IF(  MAX('Таблица_данных'[Год])= latestYear ,
   CALCULATE(SUMX(Таблица_данных,Таблица_данных[Сумма])),         
   CALCULATE(SUMX(Таблица_данных,Таблица_данных[Сумма]), ALL('Таблица_месяцев')
)))
Но теперь не бьются суммы по категориям с итоговыми в прошлые годы  :cry:
Изменено: vikttur - 28.09.2021 11:20:20
 
Думаю, Вам нужно разбить таблицу на 2 части, создать справочники,короче работать над моделью данных
я сделал это смотрите файл во вложение ,но это не лучше  вариант, надеюсь эксперты  в этой области помогут Вам.
Изменено: azma - 28.09.2021 12:17:13
 
получилось без деление таблицы, но я удалил связи между таблицами (Таблица_месяцев,Таблица_данных)

и написал такой чудовищный код:
Код
=
VAR _Mxyear =
    CALCULATE ( MAX ( 'Таблица_данных'[Год] ), ALL ( 'Таблица_данных' ) )
VAR _X1 =
    CALCULATE (
        SUM ( 'Таблица_данных'[Сумма] ),
        FILTER ( 'Таблица_данных', 'Таблица_данных'[Год] <> _Mxyear )
    )
VAR _X2 =
    CALCULATE (
        SUM ( 'Таблица_данных'[Сумма] ),
        FILTER ( 'Таблица_данных', 'Таблица_данных'[Год] = _Mxyear ),
        INTERSECT (
            VALUES ( 'Таблица_данных'[№ Месяца] ),
            VALUES ( 'Таблица_месяцев'[№ Месяца] )
        )
    )
RETURN
    IF (
        HASONEFILTER ( 'Таблица_данных'[Год] ),
        IF ( MAX ( 'Таблица_данных'[Год] ) <> _Mxyear, _X1, _X2 ),
        _X1 + _X2
    )
Изменено: azma - 28.09.2021 15:06:27
 
Цитата
azma написал: и написал такой чудовищный код:
На самом деле всё было куда проще, я пошагово исключая элементы формулы нашел проблему.
Я видоизменил вычисление последнего года для сравнения следующим образом:
Код
CALCULATE(MAX('Таблица_данных'[Год]),ALL(Таблица_месяцев[№ месяца]))

Так как если оставить предыдущий вариант
Код
MAX('Таблица_данных'[Год]) 

мера ищет только последний год в пределах выбранных в слайсере месяцев и если например данные начинаются для каждой строки не с первого месяца, то при выделении только первого месяца мера выводит всю годовую сумму в промежуточный итог.
Код
=Var latestYear = CALCULATE(MAXX('Таблица_данных', 'Таблица_данных'[Year]), ALL('Таблица_данных')) 
Return 
       IF(
           CALCULATE(MAX('Таблица_данных'[Год]),ALL(Таблица_месяцев[№ месяца]))= latestYear ,
   CALCULATE(SUMX(Таблица_данных,Таблица_данных[Сумма])),         
   CALCULATE(SUMX(Таблица_данных,Таблица_данных[Сумма]), ALL('Таблица_месяцев')
))

Всем огромное спасибо за помощь!
 
Цитата
Barly написал:
всё было куда проще
Разве таким образом итог по строкам корректный?

Изменено: azma - 29.09.2021 05:07:03
 
На моей более сложной модели и реальных данных да. Связь при этом с таблицей месяцев сохранена.
Страницы: 1
Наверх