Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Деление вычисленного значения на связанное значение из другой таблицы DAX
 
#1
12.09.2023 10:39:44
Всем привет!

Есть 3 таблицы. 1 - факт и два справочника. Справочники имеют соответствующие связи с таблицей фактов 1 ко многим.

Необходимо провести расчет используя меру. Мера = (процедура1 * значение из справочника процедур1 ... + ... процедура(n) * значение из справочника процедур(n) ) / соответствующее значение из справочника по дате.

Как сделать первое действие я разобрался. В файле подготовлена модель данных.
Код
Мера1 = SUMX('Таблица1';'Таблица1'[Процедура] * RELATED('СправочникПроцедуры'[значение]))

Далее нужно поделить получившееся значение на соответствующее значение из справочника дат

Мера 2 у меня с ошибкой
Код
Мера2=(SUMX('Таблица1';'Таблица1'[Процедура] * RELATED('СправочникПроцедуры'[значение])))/ RELATED('СправочникДаты'[Значение])

Понимаю что RELATED работает с другой функцией SUMX, но обернуть корректно не получается

формула
Код
Мера2=SUMX('Таблица1';'Таблица1'[Процедура] * RELATED('СправочникПроцедуры'[значение]) / RELATED('СправочникДаты'[Значение]))

Считает не правильно, так делит произведение по каждой строке, а нужно итоговое...

Скорее всего нужно использовать условия: Filter, If. Но у меня с ними пока туго в DAX. Возможно есть более красивое решение?
Использовать вычисляемые столбцы не хочу, так как реальный массив довольно большой.  
Изменено: Сергей Жуков - 12.09.2023 11:31:34
 
 
 
#2
12.09.2023 11:25:18
Код
мера 2:=
SUMX (
    'Таблица1';    
        'Таблица1'[Процедура] * RELATED ( 'СправочникПроцедуры'[значение] ) / RELATED ( 'СправочникДаты'[Значение] )    
)
 
 
 
#3
12.09.2023 11:31:10
Vladimir Ch, К сожалению эта формула ничем не отличается от моей формулы в 3ем примере. Одну скобку впереди формулы я поставил ошибочно.
Произведение таким образом делится также построчно. А нужно чтобы поделилось каждое итоговое по месяцу один раз на значение из соответствующего месяца
 
 
 
#4
12.09.2023 11:49:09
Цитата
Сергей Жуков написал:
К сожалению эта формула ничем
я в примере смотрел - там была ошибка, я ее и исправил. Не заметил, что это уже прописано
 
 
 
#5
12.09.2023 11:52:16
Такой вариант попробуйте:
Код
=DIVIDE(
   SUMX ( 'Таблица1'; 'Таблица1'[Процедура] * RELATED ( 'СправочникПроцедуры'[значение] ));
   MAXX('Таблица1'; RELATED ( 'СправочникДаты'[Значение] ) )
)
Изменено: Vladimir Ch - 12.09.2023 11:59:45
 
 
 
#6
12.09.2023 12:15:26
Vladimir Ch, стойте :)
Цитата
Сергей Жуков написал:
Считает не правильно, так делит произведение по каждой строке, а нужно итоговое...
Не согласен. Иначе логика вообще не ясна.

Допустим, что выбран один месяц (в рамках которого одно значение в СправочинкДаты):
Считаем построчно: x1/value + x2/value + .. + xn/value.
В школе же учились? x1/value + x2/value + .. + xn/value = (x1 + x2 + .. + xn) / value.
Поэтому построчно мы будем считать или нет - неважно :)

Допустим, выбрано 2 мес.
Для простоты будем считать, что для каждого месяца по одной строке.
Если бы Вы выбирали только первый месяц, то считали бы x1/150
Если бы Вы выбирали только второй месяц, то считали бы y1/160
Так какой результат нужен для двух (нескольких) месяцев?
Почему не подходит x1/150 + y1/160 (построчно именно так и посчитает)?

P.S. Предложенный Vladimir Ch вариант в #5 посчитает аналогично построчному для одного месяца.
Но для нескольких из всех значений в календаре в соответствии с выбранным периодом найдет максимальное и поделит на него. То есть из примера выше (x1+y1)/160. И? Результат за 2 месяца вообще ничего общего не имеет с результатами по месяцу. Где смысл такой метрики?

Я считаю, что какой-то бред. Для одного месяца разницы нет. А для нескольких ТС должен пояснить логику.
Ну и бред же, что значение для процедур берем значение для именно этой процедуры, а для месяца (в случае нескольких месяцев) какое-то непонятное.
Изменено: surkenny - 12.09.2023 12:31:05
 
 
 
#7
12.09.2023 13:21:21
Vladimir Ch, Вашу меру из #5 можно упростить:
Код
DIVIDE(
   SUMX ( 'Таблица1'; 'Таблица1'[Процедура] * RELATED ( 'СправочникПроцедуры'[значение] ) );
   MAX ( 'СправочникДаты'[Значение] )
)

Но в примере ТС увидим отличие :)
Все потому, что даты выносятся не из справочника (помним о направлении фильтра).
Сергей Жуков, в измерения выносите даты именно из справочника. Не нужно из таблиц фактов. Иначе совершите ошибки или придется  сильно усложнять меры.
Изменено: surkenny - 12.09.2023 13:21:38
 
 
 
#8
12.09.2023 13:52:49
Vladimir Ch, нет, считает тоже не правильно(

surkenny,попробую разжевать на примере. Логику самой меры пока не буду доводить. Она нам в общем-то и не нужна.

математическая формула:

Мера = (процедура1 * значение из справочника процедур1 + ... + процедура(n) * значение из справочника процедур(n) ) / соответствующее значение из справочника по дате.

Допустим берем апрель.

1)Нам нужно сначала перемножить значения таблицы фактов на значения таблицы справочника процедур (только за апрель)
2) делим получившееся значение на значение из таблицы дат.

То есть для процедуры 4 за месяц январь должно получиться:
(32*10 + 1*10) / 150 = 2,2

Вы правы, что, деля строки в разрезе процедур вот так:

32*10 / 150 + 1 * 10 / 150 = 2,2

Дает то же значение.
Но я так понимаю

Формула
Код
мера 2:=
SUMX (
    'Таблица1';    
        'Таблица1'[Процедура] * RELATED ( 'СправочникПроцедуры'[значение] ) / RELATED ( 'СправочникДаты'[Значение] )    
)
и
Код
=DIVIDE(   SUMX ( 'Таблица1'; 'Таблица1'[Процедура] * RELATED ( 'СправочникПроцедуры'[значение] ));
   MAXX('Таблица1'; RELATED ( 'СправочникДаты'[Значение] ) )
)

Дают нам не тот результат так как деление происходит непонятным образом.

в сводной мы видим значение 0,533333333
арифметикой я не смог его получить)
Близко примерно так
32*10/(150+160+165+140) + 1*10 / (150+160+165+140) = 0,5365
Как будто почему то в расчет берутся даже не связанные значения по справочнику месяцев..
Изменено: Сергей Жуков - 12.09.2023 14:00:22
 
 
 
#9
12.09.2023 14:07:56
Цитата
написал:
Сергей Жуков , в измерения выносите даты именно из справочника. Не нужно из таблиц фактов. Иначе совершите ошибки или придется  сильно усложнять меры.
Да, конечно. Только из справочника, в этом же и суть Related.
Ваша упрощенная формула же делит только на максимальное значение из таблицы справочника дат..
Но даже если так, я все равно не понимаю, почему итоговое значение по 4ой процедуре за январь не 330 / 165 = 2
 
 
 
#10
12.09.2023 15:10:53
Цитата
Сергей Жуков написал:
Дают нам не тот результат так как деление происходит непонятным образом.
потому что
Цитата
Сергей Жуков написал:
Как сделать первое действие я разобрался. В файле подготовлена модель данных.
не соответствует тому, что вы пишите здесь (даже без деления):
Цитата
Сергей Жуков написал:
(32*10 + 1*10) / 150 = 2,2
А я Вам поверил, раз пишите, что с первой частью справились. Так напишите меру:
Код
=SUMX (
    'Таблица1';    
        'Таблица1'[Значение]* RELATED ( 'СправочникПроцедуры'[значение] ) / RELATED ( 'СправочникДаты'[Значение] )    
)
 
 
 
#11
12.09.2023 15:49:41
c 5ой попытки ChatGPT написал нужную меру))

= VAR TotalWeightedSum =

   SUMX(

       'Таблица1';

       SUMX(

           FILTER('СправочникПроцедуры'; 'СправочникПроцедуры'[Процедура] = EARLIER('Таблица1'[Процедура]));

           'Таблица1'[Значение] * RELATED('СправочникПроцедуры'[значение]))

   )

VAR TotalValue3 =

   SUMX(

       FILTER('СправочникДаты';'СправочникДаты'[Дата] IN VALUES ('Таблица1'[дата]));

       'СправочникДаты'[Значение]

       )

RETURN

DIVIDE(TotalWeightedSum; TotalValue3; 0)

Изменено: Сергей Жуков - 12.09.2023 15:51:01
 
 
 
#12
12.09.2023 17:35:45
Сергей Жуков, Вы сами понимаете, что Вам ChatGPT написал? Удачи с вложенными итераторами на нормальных объемах:)
Функцию EARLIER вообще нет смысла использовать с наличием переменных.
Цитата
Сергей Жуков написал:
Да, конечно. Только из справочника, в этом же и суть Related.
При чем тут суть related?
Ощущение, что Вы вообще не понимаете, что делаете :)
У вас в примере месяцы в столбцах НЕ ИЗ СПРАВОЧНИКА!

P.S. Я промолчу о пользе значения меры chatGPT. Вы хотя бы поняли бы вначале, что Вам посчитать нужно.
Скорее всего, результат похож на тот, что Вам нужен, но он неверный :)
За такие меры я бы увольнял аналитиков :) В первой переменной 3 вложенных итерации, которые совсем ненужны. Это смерть Вашего отчета на большом объеме. Или, как минимум, боль пользователей от тормозов.
Изменено: surkenny - 12.09.2023 17:55:15
 
 
 
#13
13.09.2023 07:10:48
Сергей Жуков, а Вы вообще проверяете решения, что Вам помогающие делают??? Вот Вы пишите
Цитата
Сергей Жуков написал:
c 5ой попытки ChatGPT написал нужную меру))
а ничего, что я Вам дал правильное решение буквально в предшествующем сообщении №10:
Код
=SUMX (
    'Таблица1';    
        'Таблица1'[Значение]* RELATED ( 'СправочникПроцедуры'[значение] ) / RELATED ( 'СправочникДаты'[Значение] )    
)

результат такой же, что Вам насоветовал с многоэтажным кодом чат GPT. Выводы делайте сами.
 
 
 
#14
13.09.2023 09:24:58
Цитата
написал:
результат такой же, что Вам насоветовал с многоэтажным кодом чат GPT. Выводы делайте сами.
Не тот же
 
 
 
#15
13.09.2023 09:26:14
surkenny, СпасибО! Учту в работе))
 
 
 
#16
13.09.2023 09:53:50
Цитата
Сергей Жуков написал:
Не тот же
можете подтвердить?
П.С. про общие итоги я молчу, т.к. похоже Вы и сами пока не знаете, как они должны считаться
Изменено: Vladimir Ch - 13.09.2023 09:56:08
 
 
 
#17
13.09.2023 10:13:03
Vladimir Ch, не совсем. Для одного месяца - да. Результаты совпадут.
Суть вычисления для двух месяцев в двух столбцах: x/a и y/b.
Если же к таблице добавить столбец итогов (ну или столбцы не по месяцам, а по кварталам сделать), то
Ваша мера считает x/a + y/b
Мера ChatGPT - (x+y)/(a+b)
На скрине отличия в итогах (в месяцах значения не отличаются).

Сергей Жуков, а теперь фокус. Я заменил месяцы на месяцы из справочника.
И тормозной бред, сгенерированный ChatGPT, превращается в тривиальную меру.
Код
мера S:=
DIVIDE(
   SUMX ( 'Таблица1'; 'Таблица1'[Значение] * RELATED ( 'СправочникПроцедуры'[значение] ) );
   SUM ( 'СправочникДаты'[Значение] )
)

Согласитесь, что в таком случае прямо сразу вся логика меры понятна. А не трехэтажная хрень ChatGPT.
Изменено: surkenny - 13.09.2023 10:58:45
 
 
 
#18
13.09.2023 10:43:30
Цитата
surkenny написал:
Ваша мера считает x/a + y/bМера ChatGPT - (x+y)/(a+b)
так я  про то и писал, что описания расчета для нескольких месяцев нет. И поэтому считает данная мера правильно или нет, я лично не мог понять. Либо я не увидел это описание для группы месяцев..
 
 
 
Страницы: 1
Наверх