Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
DAX: Посчитать среднее по столбцу в связанной таблице с фильтром по текущей таблице
 
#1
20.07.2017 15:14:42
Добрый день!

Не могу сам справиться.
Поиском пользовался, не нашел, как сформулировать тоже пока не очень понимаю.

В таблице Показатели создал меру:
Код
Средний чек:=CALCULATE(AVERAGE('Услуги'[Чек]);'Показатели'[Отключен]=1)

Хочу посчитать средний чек отключенных клиентов.
Информация о чеке хранится в таблице "Услуги", которая связана с таблицей "Показатели" связью: Показатели[Услуга]=Услуги[Код]. При этом для одной записи таблицы "Услуги" есть множество связанных записей в таблице "Показатели".

Но если эту меру посчитать, то она возвращает среднее по всем строкам таблицы "Услуги" вне зависимости от контекста, даже фильтр 'Показатели'[Отключен]=1 здесь не работает. Как будто связь вообще не задействована.

Подскажите пожалуйста,
как правильно посчитать среднее по значениям в связанной таблице "Услуги" для всех связанных записей с таблицей "Показатели"?
Изменено: vetrintsev - 20.07.2017 15:15:48
 
 
 
#2
20.07.2017 15:22:02
Пример выложите, скорее всего потеряли в мере ALL
 
 
 
#3
20.07.2017 16:37:47
Цитата
Илья Демид написал:
Пример выложите
Прикладываю...

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

Делал в 2016 офисе.
Изменено: vetrintsev - 20.07.2017 16:38:11
 
 
 
#4
20.07.2017 21:38:17
Доброе время суток.
Как-то так.
Успехов.
 
 
 
#5
20.07.2017 23:06:28
Андрей, благодарю за помощь!
Заработало)

Еще не до конца уловил логику контекста, нашел книгу, которую мне рекомендовал Максим Зеленский "The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI" - буду учить.
 
 
 
#6
21.07.2017 16:05:39
vetrintsev, чтобы понять, что сделал Андрей VG, и как это работает, придется дочитать как минимум до 10 главы включительно :) Это один из самых сложных  аспектов DAX :)
F1 творит чудеса
 
 
 
#7
21.07.2017 16:30:22
Цитата
Максим Зеленский написал:
то один из самых сложных  аспектов DAX
Максим, я бы так и не сказал. Обычный Inner Join таблицы и подзапроса с фильтром с последующей группировкой и агрегатной функцией, если рассматривать это с точки зрения SQL. Там в DAX других тёмных мест хватает. :(
 
 
 
#8
23.07.2017 06:59:10
Цитата
Андрей VG написал:
Обычный Inner Join таблицы и подзапроса с фильтром с последующей группировкой
Возможно оно внутри так просто и выглядит :) но я ни как не могу понять как эта функция проворачивает запрос именно с такой логикой ))) я вижу функцию, и не понимаю что она возвращает. Вот что возвращает FILTER ??? Таблицу? Параметры фильтрации? или что то еще? И почему не работает такое же условие без FILTER? Я тут же вспомнил про функцию KEEPFILTERS, которая сработала в другой логике... Но почему???)))))) в общем, читаю...
 
 
 
#9
23.07.2017 11:45:44
Доброе время суток.
Цитата
vetrintsev написал:
Вот что возвращает FILTER ??? Таблицу?
Фильтр возвращает таблицу. Ваша проблема в том, что вы воспользовались "ленивой" версией CALCULATE. Собственно, всегда логическое выражение отбора оборачивается в фильтр. Ваша мера в "неленивой" полной версии
Код
Средний чек:=CALCULATE(AVERAGE('Услуги'[Чек]);
    FILTER('Услуги'; 'Показатели'[Отключен]=1) 
)

Но может ли таблица Показатели фильтровать Услуги, учитывая направление связей? Правильный ответ - нет, в направлении многие ко одному "пропаганды" нет :)
CALCULATE же использует отфильтрованную таблицу по условию для вычисления пересечения с таблицей, используемой в агрегатной функции (тот самый Inner Join). Но по вашему фильтру вы получаете неизменённую таблицу Услуги. Потому и не работает.
Цитата
vetrintsev написал:
Но почему???)))))
А это уже тема скорее для курилки. В рамки вашего вопроса не попадает.
 
 
 
#10
23.07.2017 12:41:46
Цитата
Андрей VG написал: Ваша мера в "неленивой" полной версии
в полной версии начальная мера выглядит так:
Код
Средний чек:=CALCULATE(AVERAGE('Услуги'[Чек]);    FILTER(ALL('Показатели'[Отключен]); 'Показатели'[Отключен]=1) )

"Обратная пропаганда" против направления связи срабатывает только в случае если мы первым аргументом FILTER запихнем таблицу Показатели целиком.
Но на самом деле там не такой механизм, и ни чего против течения не идет. То есть, описать его через inner join можно, но это не то, что происходит под ковром. Если представить, что к Услугам через 1:много привязана еще одна факт-таблица, то можно было бы и так написать:
Код
CALCULATE(COUNTROWS('Другая таблица');    FILTER('Показатели'; 'Показатели'[Отключен]=1) )
F1 творит чудеса
 
 
 
#11
24.07.2017 09:20:35
Цитата
Максим Зеленский написал:
в полной версии начальная мера выглядит так:
Спасибо, Максим - фактически никогда "ленивой" версией не пользовался.
Цитата
Максим Зеленский написал:
запихнем таблицу Показатели целиком.
Опять, же всегда использовал полную таблицу. Так и не осилил Definitive... - времени нет. Но помня, что авторы сначала предлагали для обработки многие ко многим, а потом в этой книге спокойно использовали ограничение по полной таблице... Поэтому
Цитата
Максим Зеленский написал:
но это не то, что происходит под ковром
вызывает некоторые сомнения. Да
Код
CALCULATETABLE(VALUES('Показатели'[Услуга]); FILTER('Показатели'; 'Показатели'[Отключен]=1))
не даёт требуемого пересечения с таблицей Услуги, но не факт, что причина не лежит в той же плоскости, в которой нельзя одно столбцовую виртуальную таблицу использовать в SUM.
 
 
 
#12
24.07.2017 13:38:45
В общем, дальше действительно для курилки, эту ветку мы переисчерпали :)
F1 творит чудеса
 
 
 
Страницы: 1
Читают тему
Наверх