Страницы: 1
RSS
Определение количества отфильтрованных наборов координат по усредненным результатам в Power Pivot
 

Имеется следующие данные:

В точках с координатами X,Y,Z указаны: результат опыта (1 или -1) и дата его проведения. Каждая точка, в зависимости от значения координат, принадлежит к определенной области, зависимость координат и областей задана отдельной таблицей.

Задача - посчитать количество наборов X,Y,Z, соответствующих критерию фильтра.

Фильтр – суммарное (с даты начала эксперимента) количество положительных исходов должно быть больше заданного числа. Задача для отдельного набора координат (X,Y,Z) решена.

Но!

Так как заданное число должно быть больше 50, принято решение брать суммарное количество по окрестности точки X,Y,Z. Т.е. сумма положительных исходов для точек с координатами (X,Y,Z); (X+5,Y,Z) ; (X-5,Y,Z); (X,Y+5,Z); (X,Y-5,Z); (X,Y,Z-5) ; (X,Y,Z+5) сравниваете с заданным числом.

Решение задачи в такой постановке для необходимой формы отчета зашло в тупик.

Бьюсь уже третью неделю. Прошу помощи

Изменено: Ivan Degt - 08.10.2021 19:10:41
 
Доброе время суток.
Таки наковырял неспешный вариант
Код
=Var latestDate = MAX('Sp_Date'[Date])
Var allNeededPoints = CALCULATETABLE(
    GROUPBY('Data 1'; 'Data 1'[X]; 'Data 1'[Y]; 'Data 1'[Z]);
    FILTER(ALL('Sp_Date'); 'Sp_Date'[Date] <= latestDate);
    'Data 1'[Rez] = 1
)
Var cTable = SELECTCOLUMNS(allNeededPoints; "Xc"; 'Data 1'[X]; "Yc"; 'Data 1'[Y]; "Zc"; 'Data 1'[Z])
Var oTable = SELECTCOLUMNS(allNeededPoints; "Xo"; 'Data 1'[X]; "Yo"; 'Data 1'[Y]; "Zo"; 'Data 1'[Z])
Var crossTable = CROSSJOIN(cTable; oTable)
Var usePoints = FILTER(crossTable;
    ABS([Xc] - [Xo]) < 6 && ABS([Yc] - [Yo]) < 6 && ABS([Zc] - [Zo]) < 6
)
Return COUNTROWS(usePoints)

P. S. Господам модераторам - отдельное спасибо.
 
  Большое спасибо за ответ!

Насколько я понял ваш вариант работает следующим образом:
1. Из таблицы с данными создается таблица. Например такая:

2. Формируются две таблицы:

3. Применяется функция CROSSJOIN для таблиц, полученных в предыдущем шаге:

и подсчитывается количество строк получившейся таблицы, отфильтрованной по условию ABS([Xc] - [Xo]) < 6 && ABS([Yc] - [Yo]) < 6 && ABS([Zc] - [Zo]) < 6.

Для рассматриваемого примера результат выполнения равен 8.
Честно говоря не понял что означает данный результат.

Тем не менее,
еще раз огромное спасибо за уделенное время.

P.S.
На показанном примере продемонстрирую как необходимо решить задачу.
Для простоты рассмотрим плоскость. Полученные данные на ней выглядят следующим образом:

После усреднения по окрестности каждой из точек плоскости картина должна получится следующей:

P.S.S. Добавление картинок не победил. Файл ecxel прилагаю.
P.S.S.S Победил.  
Изменено: Ivan Degt - 10.10.2021 21:34:32
 
Ivan Degt,  :D  :D  :D
Вставляйте картинки в Point, редактируйте при необходимости и сохраняете в png. На форум грузите как файл и жмете на загруженный файл
 
Цитата
Ivan Degt написал:
Честно говоря не понял что означает данный результат.
Значит я не угадал. Плохой из меня телепат.  :D
P. S. Так и не понял - что означает усреднение.
 

В данном случае под усреднением подразумевается следующее.


Для точки с координатами X, Y, Z (красный куб на рисунке) усредненным значением будет сумма результатов опытов в точках с координатами:

(X,Y,Z); (X+5,Y,Z) ; (X-5,Y,Z); (X,Y+5,Z); (X,Y-5,Z); (X,Y,Z-5) ; (X,Y,Z+5),

т. е. для всех белых кубов и красного.

Пример:

Предположим на текущую дату имеется следующий результат опытов:


Усреднённое значение для точки с координатами 50, 45, 60 будет равно 8

 
Цитата
Ivan Degt написал:
Усреднённое значение для точки с координатами 50, 45, 60 будет равно 8
Цитата
Ivan Degt написал:
Для рассматриваемого примера результат выполнения равен 8.
Так выходит Андрей всё же всё правильно сделал?
 
Цитата
Неопытный_Экселист написал:
Так выходит Андрей всё же всё правильно сделал?
это случайное совпадение цифр.
 
Цитата
Ivan Degt написал:
это случайное совпадение цифр.
Вот и надо готовить пример, чтобы не было случайных совпадений. Модель - она не любит где-то так и как-то здесь. Иначе всегда выходит этакое нечто. :)
P. S. И да, приводя уточняющий пример, не стесняйтесь показать - каков будет результат вычислений.
Изменено: Андрей VG - 11.10.2021 22:12:37
 

Невероятно, но работает!

Сумма усредненных значений положительных исходов в точках за заданный период считается.

Мое почтение! И благодарю за уделенное время.

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

Не знаю, насколько это будет информативно, но итоговый отчет должен иметь следующий вид:


P.S. Это нормально, что для таблицы данных порядка 1000 строк, расчет занимает около 5 минут?

 
Цитата
Ivan Degt написал:
занимает около 5 минут?
Нет, не нормально. Вообще, у меня есть ошибка в мере, где-то теряется одна точка. Но суть не в ней. По той схеме для плоскости, что вы приложили - получается, что каждая точка, удовлетворяющая условию, вносит в пространство координат по 1, конкретно в точки (x, y); (x - 5, y); (x + 5, y); (x, y - 5); (x, y + 5).  Следовательно, результат сумма всех таких единиц, создаваемых множеством точек 'Dat 1', которые удовлетворяют условию отбора. Соответственно, а зачем тогда находить пересечение крестов всех точек координатного пространства, как сделано у меня? Не проще ли умножить на 7 общее число точек?
Код
мера 1:=Var latestDate = MAX('Sp_Date'[Date])
Var allNeededPoints = CALCULATETABLE(
    GROUPBY('Data 1'; 'Data 1'[X]; 'Data 1'[Y]; 'Data 1'[Z]; 'Data 1'[DYXY]);
    FILTER(ALL('Sp_Date'); 'Sp_Date'[Date] <= latestDate);
    'Data 1'[Rez] = 1
)
Return 7 * COUNTROWS(allNeededPoints)
Цитата
Ivan Degt написал:
А именно: посчитать количество точек, в которых усредненное значение положительных исходов больше заданного числа.
Каких точек? Вы не забывайте давать определения.
Изменено: Андрей VG - 13.10.2021 08:23:30
 
Цитата
Вообще, у меня есть ошибка в мере, где-то теряется одна точка.
Ошибка видимо в этом месте.
было:
Код
        ROW("key"; FORMAT(Xc - 5; "000") & " " & FORMAT(Yc; "000") & "|" & FORMAT(Zc; "0000"));
стало:
Код
        ROW("key"; FORMAT(Xc - 5; "000") & "|" & FORMAT(Yc; "000") & "|" & FORMAT(Zc; "000"));
Цитата
Следовательно, результат сумма всех таких единиц, создаваемых множеством точек 'Dat 1', которые удовлетворяют условию отбора. Соответственно, а зачем тогда находить пересечение крестов всех точек координатного пространства, как сделано у меня?
Именно поэтому к сожалению ваша волшебная формула не подошла.

Цитата
Каких точек? Вы не забывайте давать определения.
Попробую на примере своего решения (не полного) показать суть задачи.
Рассмотрим вот такую сводную (для экономии места показана ее часть):
Табл.1

Необходимо:
1. для каждой точки (соответственно строки данной таблице)  определить усредненное кол-во положительных исходов (колонка  "Result(3x3)");
2. сравнить это число с заданным (в данном случае это 2, т.к. данных мало);
3. подсчитать количество отфильтрованных по условию п.2 точек (для показанного фрагмента оно равно 3);
4. Получить распределение цифр п. 3 в зависимости от области ("Scen") и даты. Т. получить сводную вот такого типа:
Табл.2


Небольшие пояснения к Табл.1.
В колонке  "Result(3x3)" результат вычисления среднего значения по моей формуле, в колонке "мера 1" - результат вашей. Как видно общий итог совпадает, однако становится понятно что ваш подход (безусловно верный) не применим к решению задачи в такой постановке.

Теперь о проблемах моего не до решения.
1. Колонка "Filtr" не суммируется.
2. Соответственно в необходимом отчете данные неверные.

Хотя подозреваю что причина ошибки не только в пункте 1.
Страницы: 1
Наверх