Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Дмитрий Никитин
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
Предотвратить задубливание количества товаров в сводной
 
#
25.02.2025 17:20:34
Коллеги, добрый день!

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

По каждому товару есть сведения о количестве и всех платежах по этому товару: стоимость товара, доставка, хранение и т.д.
Далее я строю по таблице сводную, но из-за того, что товары в накладной у меня дублируются по количеству платежей, привязанных к этому товару, дублируется и их количество, и когда я вывожу в строки ПТиУ и товары без наименований платежей, а количество товаров кидаю в значения, то у меня соответственно выдают в разрезе ПТиУ - товар неправильное (задублированное) количество.

Подскажите, пожалуйста, как решить эту проблему?

Сразу скажу, что вытянуть наименования платежа в столбцы - не вариант: их в реальной задаче гораздо больше, к тому же они ещё дополнительно поделены по признаку: облагаются НДС/ не облагаются НДС.

Мне кажется, можно задать какую-то меру, которая бы правильно считала количество в разрезе ПТиУ или  ПТиУ-товар, но, к сожалению, я почти забыл  DAX за полгода его неиспользования... Помогите, пожалуйста, решить эту проблему.
Шлюхогон42
 
Перейти    
Максимальное количество столбцов в Power.Query, Не смог найти информацию о максимально допустимом количестве столбцов в Power.Query
 
#
09.10.2024 10:41:30
Коллеги, здравствуйте!

Получил в работу своеобразную базу с ценами на товары с 2011-го года. Самый быстрый способ её нормализации - через транспонирование с последующим анпивотом. Но у меня есть сомнения в плане лимита по количеству столбцов в Power Query. База будет обновляться, и не получится ли в какой-то момент так, что при транспонировании портянки с ценами превысится максимальный лимит на количество столбцов?  
Шлюхогон42
 
Перейти    
Power.Query. Оптимизация запросов с выгрузкой динамики курсов с сайтов ЦБ РФ, РБ и КЗ, Помощь в оптимизации запросов с выгрузкой динамики курсов валют с сайтов центральных банков с помощью функции Table.Buffer
 
#
07.10.2024 13:44:13
Коллеги, здравствуйте!

Помогите, пожалуйста, оптимизировать существующие запросы-подключения к сайтам ЦБ РФ, РБ и КЗ.

Имеется прайс по различным товарам за различные даты. Товары закупаются в России, Беларуси и Казахстане. Цены в прайсе приведены как в российских, так и в белорусских рублях, а также в тенге. Причём цены на товары, закупаемые в Беларуси могут быть указаны как в белорусских рублях, так и в российских. Тоже самое с ценами на товары, закупаемые в Казахстане. Причём сами товары могут закупаться как в национальной валюте, так и в рублях, так и в евро, долларах и юанях. Следовательно, если цены даны в российских рублях, а закупить товары нужно в Белоруссии причём в евро, требуется пересчёт курса евро в российские рубли, но по курсу ЦБ РБ российского рубля по отношению к белорусскому рублю.

Я все необходимые кросс-курсы сделал, к прайсу подтянул. Но ожидаемо всё очень долго подгружается. Я, где смог, добавил Table.Buffer, но не уверен в том, что в плане оптимизации и ускорения сделал всё, что было возможно. Прошу уважаемых знатоков Power Query помочь с оптимизацией.

Валюты тяну с 11-го года, так как статистика ведётся с этого года и для анализа требуются данные за прошлые годы.
Шлюхогон42
 
Перейти    
DAX. Влияние фильтра по подкатегориям на меру с фильтром по категориям., Не понятна механика фильтрации мер, рассчитанных по категориям, в срезах по подкатегориям
 
#
08.09.2024 18:02:50
Здравствуйте!

Прошу помощи в решении очередного вопроса по DAX.

Есть таблица Product, где содержится информации о товарах: названия, бренды, цвета, категории и подкатегории.

Создал простую меру:
Код
Кол-во подкатегорий := DISTINCTCOUNT('Product'[Subcategory])
В разрезе категорий и подкатегорий мера отрабатывает верно: по категориям показывает кол-во входящих подкатегорий, по подкатегориям - единицы

Создал меру посложнее:
Код
Кол-во подкатегорий_1 = 
SUMX(
    VALUES('Product'[Category]),
    CALCULATE(DISTINCTCOUNT('Product'[Subcategory])))
Логика такая:
1) в разрезе категорий - аналогично предыдущей мере;
2) в разрезе подкатегорий - рассуждаю так:

На первый аргумент VALUES('Product'[Category]) влияет фильтр по подкатегории. Подкатегория фильтрует категорию, в которую она входит. Для "Bluetooth Headphones"  - это категория "Audio".

На второй аргумент  CALCULATE(DISTINCTCOUNT('Product'[Subcategory])) влияет внешний фильтр - подкатегория "Bluetooth Headphones" и внутренний фильтр - категория "Audio". Внутренний фильтр важнее внешнего, значит CALCULATE(DISTINCTCOUNT('Product'[Subcategory])) должен рассчитываться по категории  "Audio"

Таким образом по каждой подкатегории: "Bluetooth Headphones", "MP4&MP3" и т.д. значение меры должна быть 3, но выдаёт почему-то 1

Помогите, пожалуйста, разобраться: какова правильная логика расчёта меры?
Изменено: Дмитрий Никитин - 08.09.2024 18:03:50
Шлюхогон42
 
Перейти    
DAX. Накопительный итог с помощью FILTER и CALCULATETABLE, Не считается накопительный итог по товарам при использовании функции CALCULATETABLE
 
#
05.09.2024 20:06:36
Здравствуйте, коллеги!

Для последующего ABC-анализа нужно посчитать накопительный итог по товарам.
Обычно создавал вычисляемый столбец с помощью функции FILTER:
Код
Накопленная прибыль = 
VAR p = 'Товары'[Прибыль] 
VAR tab = 
FILTER(
    'Товары',
    'Товары'[Прибыль]  >= p)
RETURN
SUMX(
    tab,
    'Товары'[Прибыль])

Всё корректно считает.

Затем я попробовал ради эксперимента использовать вместо FILTER функцию CALCULATETABLE.
Код
Накопленная прибыль CALCULATETABLE = 
VAR p = 'Товары'[Прибыль] 
VAR tab =
CALCULATETABLE(
    VALUES('Товары'[Товар]),
    ALLEXCEPT('Товары', 'Товары'[Прибыль]),
    'Товары'[Прибыль]  >= p)
RETURN 
SUMX(
    tab,
    CALCULATE(SUM('Товары'[Прибыль])))

Опишу логику формулы.

CALCULATETABLE в отличие от FILTER инициирует преобразование К.С. в К.Ф., следовательно на выражение VALUES('Товары'[Товар]) действуют следующие внешние фильтры:
1. Значение в текущей строке по столбцу "Категория".
2. Значение в текущей строке по столбцу "Прибыль".

Второй и третий аргументы функции CALCULATETABLE задают внутренние фильтры:
1. Фильтровать только по столбцу "Прибыль", игнорить фильтры по остальным столбцам.
2. Весь столбец "Прибыль" (по умолчанию действует доп. условие - ALL( 'Товары'[Прибыль]) ) отфильтровать так, чтобы остались только те значения, что равны или превышают значение в текущей строке столбца "Прибыль".

Внутренний фильтр перебивает внешний, и в итоге остаются только те строки столбца 'Товары'[Товар], что удовлетворяют условиям 2-го и 3-го аргументов функции CALCULATETABLE

Далее в функции SUMX происходит преобразование К.С. в К.Ф. посредством функции CALCULATE, и значение SUM('Товары'[Прибыль]) рассчитывается по каждой строке отфильтрованного столбца VALUES('Товары'[Товар]), а затем все вычисленные значения прибыли суммируются.

По сути логика та же, что и с функцией FILTER, однако формула выдаёт значения не накопительным итогом, а текущей строки столбца "Прибыль".

Для того, чтобы убедиться в справедливости своей логики, я также создал вычисляемый столбец с накопительным итогом по товарам:
Код
Накопленные товары CALCULATETABLE = 
VAR p = 'Товары'[Прибыль] 
VAR tab =
CALCULATETABLE(
    VALUES('Товары'[Товар]),
    ALLEXCEPT('Товары', 'Товары'[Прибыль]),
    'Товары'[Прибыль]  >= p)
RETURN 
CONCATENATEX(
    tab,
    'Товары'[Товар],
    ", ")

Как и ожидалось, в каждой строке идёт накопление товаров по изложенной выше логике.

Вопрос: почему же не считается накопительный итог?  
Изменено: Дмитрий Никитин - 05.09.2024 20:06:53
Шлюхогон42
 
Перейти    
DAX. Логика табличного фильтра в мере, Хочу понять в точности логику табличного фильтра в мере, заданной с помощью функции CALCULATE и изменённой активной связью с помощью функции USERELATIONSHIP
 
#
26.07.2024 19:07:19
Здравствуйте, знатоки DAX!

Подскажите, пожалуйста, логику вычисления меры, заданной с помощью функции CALCULATE и фильтром по таблице, а не столбцу.

Пример меры:
Код
Multiple Sales := 
CALCULATE(
    [Sales Amount],
    FILTER(
        Sales,
        'Sales'[Quantity] > 1
    )
)
Данная мера использована в матрице, где в строки выведены бренды - скрин

Объясню, как я понимаю логику работы этой меры.
1. Таблица Sales фильтруется по Brand, далее фильтруется по количеству Quantity больше 1.
2. Отфильтрованная таблица поступает как условие фильтра для меры [Sales Amount]
И тут у меня вопрос: правильно ли я понимаю, что внешний фильтр по брендам не действует на меру [Sales Amount], так как таблица Sales уже отфильтрована по Brand, а так как таблица Sales расширенная по отношению к таблице Product, то включает связанный столбец Product[Brand] ?

Если так, тогда я не понимаю, почему другая мера:
Код
Delivered Amount := 
CALCULATE (
    [Sales Amount],
    CALCULATETABLE (
        Sales,
        USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] )
    )
)
выдаёт пустые значения. Мера используется в матрице, где в строки выведены годы - скрин1

Ведь тут же по идее та же логика.
1. Таблица Sales связана с таблицей 'Date' по дате доставки.
2. Таблица Sales, связанная с таблицей 'Date' по дате доставки, фильтруется по году из внешнего контекста фильтра.
3. Отфильтрованная таблица Sales поступает как условие фильтра для меры [Sales Amount]
Получается, что внешний фильтр по годам не действует на меру [Sales Amount], так как таблица Sales расширенная по отношению к таблице 'Date' и включает годы (годы доставки).

В книге "Подробное руководство по DAX" пустые значения меры объяснены тем, что таблица с привязкой к дате заказа пытается отфильтровать таблицу с привязкой к дате доставки, но откуда вдруг появилась эта таблица с привязкой к дате заказа, когда в формуле задана привязка к дате доставки, я не понимаю.

Помогите разобраться, пожалуйста!

Ссылка на файл - ссылка
Изменено: Дмитрий Никитин - 26.07.2024 19:09:25
Шлюхогон42
 
Перейти    
DAX. Покупки клиентов первого года в разрезе последующих лет, Хочу увидеть продажи тех клиентов, которые совершили покупку в первом отчётном году, в разрезе последующих лет
 
#
22.07.2024 00:21:32
Здравствуйте, знатоки DAX!

Помогите, пожалуйста, дополнить формулу.

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

Вот моя формула меры:
Код
Покупки клиентов 1-го года := 

VAR FirstYear = 
CALCULATETABLE(
        VALUES('Date'[Calendar Year Number]),
        FIRSTNONBLANK('Date'[Calendar Year Number], [Sales Amount]),
        ALL('Date'))

VAR FirstYearCustomers = 
CALCULATETABLE(
        VALUES('Sales'[CustomerKey]),
        FirstYear)

RETURN
CALCULATE(
        [Sales Amount],
        FirstYearCustomers)
Формула выдаёт неправильные результаты. Помогите, пожалуйста, найти ошибку и дополнить формулу.
Файл доступен по ссылке - https://disk.yandex.ru/d/kZ3fziTYTRUeVQ  
Шлюхогон42
 
Перейти    
RANKX и SUMX - почему работают по-разному?, Объясните: почему логика работы функции RANKX отличается от SUMX, хотя это итерационные функции, которые по идее должны работать одинаково
 
#
04.07.2024 19:09:27
Здравствуйте, знатоки DAX!

При расчёте рангов поймал себя на том, что я не понимаю до конца как работает функция RANKX. Сравнил результат работы двух функций: SUMX и RANKX. По работе SUMX - никаких вопросов, RANKX - есть вопросы.

Дана простейшая модель данных: манагеры, суммы.
Создал две меры RANKX и SUMX:
Код
RANKX := 
RANKX(
    ALLSELECTED('Заявки'[Менеджер]),
    CALCULATE(SUM('Заявки'[Сумма])))

SUMX := 
SUMX(
    ALLSELECTED('Заявки'[Менеджер]),
    CALCULATE(SUM('Заявки'[Сумма])))
Вывел в отчёт столбец "Менеджер" и две эти меры

Теперь опишу логику работы мер, как я её понимаю.

ALLSELECTED удаляет внешний фильтр - столбец "Менеджер" в вычисляемой таблице. Значит на вход обе функции получают столбец со всеми менеджерами.

CALCULATE( SUM( 'Заявки'[Сумма] ) )  - также не принимает фильтр по столбцу "Менеджер" из вычисляемой таблицы, так как это внешний фильтр, который совпадает с внутренним фильтром - менеджерами из столбца ALLSELECTED( 'Заявки'[Менеджер] )

По каждому менеджеру в столбце ALLSELECTED( 'Заявки'[Менеджер] ) происходит расчёт - SUM( 'Заявки'[Сумма] )

Затем функция SUMX агрегирует сумму по всем менеджерам и выдаёт общий результат в каждой ячейке вычисляемой таблицы.

Функция RANKX по идее должна делать то же - агрегировать результат по всем менеджерам, но каким-то образом она "понимает", что в первой строчке вычисляемой таблицы менеджер "Петров", и выдаёт ранг по этому менеджеру. Но как? Ведь ALLSELECTED удалил внешний фильтр по менеджерам, CALCULATE( SUM( 'Заявки'[Сумма] ) ) также не принял внешний контекст фильтра, так как он совпал с внутренним, а внутренний важнее внешнего.

На каком этапе, где именно функция RANKX "увидела" менеджера "Петрова"?  
Шлюхогон42
 
Перейти    
Поведение ф-ции VALUES в контексте строки, Разные результаты в вычисляемых столбцах при применении ф-ции ALLEXCEPT и ALL + VALUES
 
#
21.06.2024 20:50:29
Коллеги, привет!

Снова возник вопрос по DAX.

Подскажите, пожалуйста, почему различаются результаты в вычисляемых столбцах, если применяю в качестве 2-го аргумента в ф-ции CALCULATE конструкцию:
1) ALLEXCEPT
2) ALL + VALUES ?

Первая формула с ALLEXCEPT
Код
СуммаСчета по кл-ту = 
CALCULATE(
    SUM('фактЗаявки'[СуммаСчета]),
    ALLEXCEPT('фактЗаявки', 'фактЗаявки'[Емейл]))
выдаёт корректный результат

Вторая формула с ALL + VALUES
Код
СуммаСчета по кл-ту_1 = 
CALCULATE(
    SUM('фактЗаявки'[СуммаСчета]),
    ALL('фактЗаявки'),
    VALUES('фактЗаявки'[Емейл]))
выдаёт в каждой строке общую сумму счетов, как будто VALUES('фактЗаявки'[Емейл]) в формуле нет

И наконец, если добавляю к VALUES('фактЗаявки'[Емейл]) ф-цию CALCULATETABLE
Код
СуммаСчета по кл-ту_2 = 
CALCULATE(
    SUM('фактЗаявки'[СуммаСчета]),
    ALL('фактЗаявки'),
    CALCULATETABLE(VALUES('фактЗаявки'[Емейл])))
всё корректно считает.

Вопрос: ф-ция CALCULATE преобразует контекст строки в контекст фильтра. Значит ф-ция VALUES должна выдавать емейл текущей строки. Так как остальные фильтры сняты функцией ALL, то вся таблица 'фактЗаявки' должна фильтроваться по емейлу, то есть формула должна работать аналогично первой формуле. Почему VALUES('фактЗаявки'[Емейл]) "не видит" контекста фильтра, преобразованного из контекста строки? Почему нужно добавлять CALCULATETABLE ?
Изменено: Дмитрий Никитин - 21.06.2024 20:51:27
Шлюхогон42
 
Перейти    
Логика работы итерационной ф-ции DAX с выражением и мерой во 2-м аргументе, Логика работы ф-ции SUMX с функцией ALL в первом аргументе и выражением/мерой во втором
 
#
08.06.2024 17:31:30
Коллеги, привет!

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

Дана простейшая модель данных: менеджеры, суммы, затраты.

Задаю простейшую меру "Прибыль":
Код
Прибыль :=
SUMX(
    'Заявки',
    'Заявки'[Сумма] - 'Заявки'[Затраты])
Далее создаю меру с общей прибылью по всем менеджерам:
Код
Прибыль_ALL+Выражение = 
SUMX(
    ALL('Заявки'),
    'Заявки'[Сумма] - 'Заявки'[Затраты])
По этим мерам нет никаких вопросов.

Далее создаю меру также с таблицей ALL('Заявки') в первом аргументе, но с мерой (а не выражением) во втором:
Код
Прибыль_ALL+Мера = 
SUMX(
    ALL('Заявки'),
    [Прибыль])
И тут возникает вопрос: почему эта мера в разрезе менеджеров выдала тот же результат, что и и предыдущая?

Я размышляю так:
ф-ция SUMX принимает на вход полную таблицу "Заявки" и по каждой строке этой таблицы считает меру "Прибыль" и агрегирует (суммирует) её столько раз, сколько строк в исходной таблице "Заявки".

В случае с мерой "Прибыль_ALL+Выражение" вопросов нет. Тут во всей таблице "Заявке" выполняется построчно вычитание "затрат" из "суммы" и агрегирование (сложение) этих построчных вычислений.

В случае с мерой "Прибыль_ALL+Мера" логика должна быть как будто другая. Тут также на вход подаётся полная таблица "Заявки", но вторым аргументом - мера, а мера по умолчанию идёт с ф-цией CALCULATE. А раз так, значит мера автоматически принимает контекст фильтра.

Возьмём строку по менеджеру "Воснецова". Выражение ALL('Заявки') обнуляет фильтр по менеджеру "Воснецова", в итоге будет посчитано сумма по всей таблице "Заявки", но второй аргумент у нас мера "Прибыль", а "Прибыль" по менеджеру "Воснецова" = 141
Так не должно ли по идее суммироваться по строке "Воснецова" 10 раз (по количеству строк в таблице "Заявки") число 141 ?
Шлюхогон42
 
Перейти    
Создать в модели данных столбец с номером заказа, В модели данных Power Pivot создать вычисляемый столбец с номером заказа с помощью функции ALLEXCEPT
 
#
02.05.2024 15:51:24
Уважаемые знатоки DAX!

Помогите, пожалуйста, с формулой расчёта номера заказа.

Есть простенькая модель данных: ID клиента,  ID заказа, дата заказа и количественные характеристики заказа (они тут значения не имеют).

Я самостоятельно создал два вычисляемых столбца "Кол-во заказов" в разрезе каждого клиента.

Первый столбец - с использованием функции ALLEXCEPT:
Код
=CALCULATE(
         DISTINCTCOUNT(Orders[Order ID]);
         ALLEXCEPT(
             Orders; 
             Orders[Customer ID]))

Второй столбец - с использованием функций FILTER и EARLIER:
Код
=CALCULATE(
          DISTINCTCOUNT(Orders[Order ID]);
          FILTER(
              'Orders';
              'Orders'[Customer ID] = EARLIER('Orders'[Customer ID])))


Как я понимаю, использование функций ALLEXCEPT и FILTER + EARLIER в качестве внутреннего контекста фильтра в функции CALCULATE - равнозначно.

Затем я создал вычисляемый столбец "Номер заказа" с помощью функций FILTER и EARLIER:
Код
= CALCULATE( 
           DISTINCTCOUNT('Orders'[Order ID]); 
           FILTER( 
              'Orders'; 
              'Orders'[Customer ID] = EARLIER('Orders'[Customer ID]) && 
              'Orders'[Order Date] <= EARLIER('Orders'[Order Date])))

Вопрос: как теперь создать вычисляемый столбец "Номер заказа", но уже с помощью функции ALLEXCEPT ?
Изменено: Дмитрий Никитин - 02.05.2024 15:53:19
Шлюхогон42
 
Перейти    
Работа функции CALCULATE. Расчёт доли продаж месяца относительно года, Вопрос по языку DAX в Power BI
 
#
20.04.2024 00:37:46
Уважаемые знатоки DAX!

Снова прошу помочь объяснить, как под капотом считает функция CALCULATE.

Задачка та же, что и в предыдущем вопросе. Хочу посчитать доли продаж, но уже в разрезе месяцев по каждому году.

По-прежнему таблицы: фактЗаявки и 3 справочные: по курсам, по сотрудникам и календарь

В таблице календарь столбцы: даты, месяцы, годы.

Создаю меру "Сумма оплат"

Код
SUM('фактЗаявки'[СуммаСчета])


Затем считаю сумму продаж по каждому году - "Сумма по годам"

Код
 CALCULATE(
             [Сумма оплат],
             ALL('спрКалендарь'[Месяц]))


И вот по этой мере значения те же, что и в "Сумме оплат". Почему?

Причём конструкция

Код
 ALLEXCEPT('фактЗаявки','спрКалендарь'[Год])


решила проблему, но что не так с ALL('спрКалендарь'[Месяц]) ?
Изменено: Дмитрий Никитин - 20.04.2024 00:43:35
Шлюхогон42
 
Перейти    
Работа функции CALCULATE с фильтром по функции ALLEXCEPT, Вопрос по языку DAX в Power BI
 
#
19.04.2024 18:21:47
Уважаемые знатоки DAX!

Снова обращаюсь по поводу хитросплетений работы функции CALCULATE. На этот раз в паре с функцией ALLEXCEPT

Есть несложная модель данных:

фактЗаявки и 2 справочника: по курсам и по сотрудникам

Задачка сводится к вычислению доли продаж в разрезе курсов обучения по каждому сотруднику

Сначала вычисляю обычную меру "Сумма счёта":

Код
 SUM('фактЗаявки'[СуммаСчета])


Затем меру "Сумма счетов по сотрудникам":

Код
 CALCULATE(
 SUM('фактЗаявки'[СуммаСчета]),
 ALLEXCEPT('фактЗаявки', 'фактЗаявки'[КодСотрудника]))


Ну и меру "Доля суммы":

Код
 [Сумма счёта] / [Сумма счетов по сотрудникам]


Вопрос у меня к логике расчёта меры "Сумма счетов по сотрудникам". Параметр внутренней фильтрации я задаю с помощью функции ALLEXCEPT:

Код
 ALLEXCEPT('фактЗаявки', 'фактЗаявки'[КодСотрудника])


Логика такая: отфильтруй мне всю таблицу фактЗаявки кроме столбца КодСотрудника. По идее DAX меня должен понять, так как между таблицей спрСотрудники и фактЗаявки установлена связь 1 ко многим. Однако мера считает неправильно

Не помогает и замена фильтра по таблице справочника:

Код
  ALLEXCEPT('спрСотрудники', 'спрСотрудники'[Сотрудник])


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

Код
 ALL('спрКурсОбучения'[КурсОбучения])


Вопрос в другом: почему в данном случае не работает функция ALLEXCEPT ? Какая тут логика? Где ошибка?
Изменено: Дмитрий Никитин - 19.04.2024 18:50:49
Шлюхогон42
 
Перейти    
Работа функции CALCULATE с фильтром по функции ALL и внешними срезами, Вопрос по языку DAX в Power BI
 
#
16.04.2024 21:05:03
Уважаемые знатоки DAX!
Помогите, пожалуйста, разобраться в хитросплетениях функции CALCULATE.

Есть простейшая табличка: манагеры, сумма, затраты

Рассчитана мера "Прибыль" по формуле:

SUMX(
           'Заявки',
           'Заявки'[Сумма] - 'Заявки'[Затраты])

Также есть мера "Прибыль_все_менеджеры", рассчитанная по формуле:

CALCULATE(
                    SUMX(
                             'Заявки',
                             'Заявки'[Сумма] - 'Заявки'[Затраты]),
                     ALL('Заявки'[Менеджер]))

Ну и мера "Прибыль%" - как отношение первой меры ко второй. Она в данном вопросе не столь важна.

В представлении отчёта выведена матрица с менеджерами и этими тремя мерами

И вот я добавляю 2 среза:

1. По менеджерам
2. По сумме.

Когда я фильтрую по менеджерам: Сидоров и Скворцов, то всё логично: значения меры "Прибыль_все_менеджеры" не изменяются, а мера "Прибыль%" выдаёт по итогу 39%, так как считает процент прибыли от всех менеджеров, а не только тех, что отфильтрованы срезом.

Но вот я дополнительно фильтрую срез по сумме: от 50, и мера "Прибыль_все_менеджеры" выдаёт уже не то, что я ожидал. А именно: сумму прибыли только по Сидорову и Скворцову и только по строкам, где сумма больше 50.

К фильтру по сумме - вопросов нет. Это внешний фильтр, который никак не конфликтует с внутренним. Но почему формула начинает выдавать значения по двум менеджерам? Ведь внутренний фильтр в формуле CALCULATE - ALL('Заявки'[Менеджер]) имеет приоритет.
Шлюхогон42
 
Перейти    
Минимальный пакет Microsoft Office, чтобы был Power Pivot
 
#
31.01.2024 17:06:31
Добрый день!
У меня на домашнем компе установлен пакет Microsoft Office "для дома и учёбы 2016"
В Excel есть надстройка Power Query, но нет Power Pivot
Спрашивал в М-Видео, мне предложили какую-то хрень - office suite. Как я понял, это какая-то грубая подделка, где Excel не Excel, и хрен пойми как будет работать.
В другом интернет-магазине предложили "Microsoft Office 2021 Home and Student Medialess", но опять же, нет уверенности что в этом пакете будет Power Pivot
Подскажите, пожалуйста, минимальный пакет, чтобы в Excel были обе эти надстройки: Power Pivot и Power Query. И может быть (если правилами форума это допустимо) посоветуйте магазин, где его дешевле всего можно приобрести.
Заранее спасибо!
Шлюхогон42
 
Перейти    
Подтянуть значение по критерию НЕ равно частичному вхождению условия, Подтянуть название и дату РК, минуя незначимую РК
 
#
26.10.2023 14:42:50
Коллеги, привет! Помогите, пожалуйста!
Такая задачка. Есть у меня выгрузка по звонкам клиентов и выгрузка по визитам этих клиентов в офис продаж.
В выгрузку по визитам мне надо подтянуть рекламную кампанию по следующим условиям:
1) совпадение номеров телефонов;
2) звонок должен был быть совершён раньше или в день визита;
3) дата звонка должна быть ближайшая к дате визита
4) нужно подтянуть все РК, кроме исходящего звонка (если был только исходящий звонок - выдать пусто или "-")
Пример во вложении
Использовал формулу ИНДЕКС и ПОИСКПОЗ, но, к сожалению, excel не понимает мою конструкцию Таблица1[Рекламная кампания]<>"*Исходящий*"
Приходится предварительно либо удалять все sip в Исходящих, либо создавать отдельный столбец. Это хлопотно и затратно, хотелось бы всё как-то одним действием. Тем более, что на практике я фильтрую большое количество иных рекламных источников, кроме исходящих звонков, которые также содержат всякие кракозябры, помимо основного условия фильтра.
Изменено: Дмитрий Никитин - 26.10.2023 16:07:08 (уточнение условия задачи)
Шлюхогон42
 
Перейти    
Макрос для удаления дублей в таблице, С помощью макроса удалить дублирующие строки в таблице
 
#
07.09.2023 14:48:58
Коллеги, привет!

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

Поясню.

Есть выгрузка с номерами телефонов клиентов и датами и временем их звонков в офис продаж, а также той рекламной кампанией (в моём примере они просто пронумерованы), по которой был совершён звонок, и тем объектом, которым они интересовались (у меня это предметы мебели)

Клиенты могли звонить по несколько раз, по разным рекламным кампаниям и интересоваться разными объектами рекламы.

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

Сейчас делаю это вручную:
1) нахожу дубли, склеивая номер и объект рекламы и выделяя цветом повторяющиеся значения
2) сортирую по дате и времени звонка и цвету в столбце с дублями
3) удаляю строки

Очень бы хотелось упростить эту задачку нажатием одной кнопки.

Заранее благодарю за помощь!
Шлюхогон42
 
Перейти    
Проставить только одно значение в итоговом столбце
 
#
22.08.2023 19:53:16
Коллеги, привет! Помогите, пожалуйста!
Задачка такая:
есть столбец условно с названиями растений, напротив каждого наименования некая цифра, нужно сделать так, чтобы цифра эта проставилась в итоговом столбце только один раз и более не повторялась (см. пример во вложении)
То есть в моём примере:
у "одуванчика" циферка 1 повторяется 4 раза, а надо, чтобы в итоговой столбец попала только первая цифра 1
у "ромашки" сначала циферка 0, затем 1, затем снова 0 и снова 1, а итогом должно стать только циферка 1 напротив второй позиции ромашки в списке
у "подорожника" порядок цифер: 0,1,1, а итогом должен стать 0,1,0
и так далее.
Короче говоря, находим первое число больше 0, выносим его в итоговый столбец, а дальше по списку должны быть только нули у этого наименования растения.
Помогите, пожалуйста! Кажется, что формула должна быть не очень сложная, несколько вложений ЕСЛИ и всё, но никак не могу допереть. Может быть кто-то с похожей задачкой сталкивался?
Большая просьба - без макросов.
Заранее всем спасибо!
Шлюхогон42
 
Перейти    
Посчитать количество номеров в таблице, если их несколько в одной ячейке, Посчитать количество номеров телефонов в таблице, если их в одной ячейке несколько штук через запятую
 
#
18.07.2023 12:42:27
Коллеги, привет!
Подскажите, пожалуйста, лайфхак для такой задачки.
Мне нужно посчитать количество номеров телефонов в таблице при том, что в одной ячейке их может быть как 1, так и несколько (2,3,4...,20). Пример прилагаю.
Если б всё было по-человечески и в каждой ячейке было по 1-му номеру, то проблем нет - формула СЧЁТЕСЛИ решает задачу
Если их всегда в ячейке было б больше 1, то конструкция:
=СЧЁТЕСЛИ(Таблица1[Номера телефонов];"*"&D4&"*")
также закрывает проблему,
но их вразнобой: то 1 номер в ячейке, то несколько.
И в этом случае склеивание со звёздочкой в начале и в конце не считает номера телефонов, которые одни в ячейке.
Приходится складывать:
=СЧЁТЕСЛИ(Таблица1[Номера телефонов];"*"&D4&"*") + СЧЁТЕСЛИ(Таблица1[Номера телефонов];D4), но это крайне неудобно, когда ячеек 20 тысяч и помимо номеров телефонов есть дополнительные условия, вроде даты, какого-нибудь айдишника и проч.
Может быть есть способ решить задачу в одно действие?
Шлюхогон42
 
Перейти    
Подтянуть значение по двум условиям и ближайшей дате, Подтянуть рекламное объявление по номеру телефона и купленному товару по ближайшей дате
 
#
28.06.2023 15:37:50
Коллеги, привет!
Прошу помочь решить задачу в экселе.

Клиенты, которые ранее звонили по рекламным объявлениям и интересовались теми или иными товарами, пришли в офис продаж и приобрели некий товар
Нужно подтянуть последнее рекламное объявление, по которому они звонили и интересовались тем товаром, который в итоге купили.
В Таблице 1 представлена история звонков клиентов
В Таблицу 2 нужно подтянуть рекламное объявление и дату звонка
То есть нужно подтянуть рекламное объявление по совпадению: номера телефона и названию товара и по ближайшей дате звонка к дате покупки.
Ранее публиковал похожую задачку - https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=158491&TITLE_SEO=158491-indeks-i-poiskpoz-dlya-blizhayshey-daty-k-iskomoy, но там условия было всего два: совпадение номера телефона и ближайшая к дате покупки дата звонка.

Заранее благодарю за помощь!

P.S. Я всегда пользовался связкой ИНДЕКС и ПОИСКПОЗ:
=ИНДЕКС(Таблица1[Рекламное объявление];ПОИСКПОЗ(1;(Таблица1[Номер телефона]=[@[Номер телефона]])*(Таблица1[Интересующий товар]=[@[Купленный товар]]);0))
Но сейчас эта формула мою задачу не решает, так как я не знаю, как в неё внедрить условие по поиску ближайшей к дате покупки даты звонка.

И ещё большая просьба: желательно без макросов
Изменено: Дмитрий Никитин - 28.06.2023 16:33:23
Шлюхогон42
 
Перейти    
Индекс и Поискпоз для ближайшей даты к искомой, Подтянуть данные по совпадению номера телефона и ближайшей даты
 
#
28.06.2023 00:07:16
Добрый вечер, уважаемые форумчане!

Прошу вас помочь мне со следующей задачкой

На первом листе (Таблица 1) приведён список рекламных объявлений, по которым звонили клиенты, номер телефона звонившего и дата звонка
На втором листе (Таблица 2) содержится дата визита клиента в офис продаж и его номер телефона
Задача: нужно подтянуть по номеру телефона клиента, посетившего офис, последнее рекламное объявление, по которому был совершён звонок
Файл прилагаю.

Объясню свои затруднения.

Если отсортировать Таблицу 1 по номерам телефонов и дате звонка от старых к новым, а затем воспользоваться стандартной формулой:
=ИНДЕКС(Таблица1[Рекламное объявление];ПОИСКПОЗ([@[Номер телефона]];Таблица1[Номер телефона];0))
то формула подтянет мне объявление по самому первому звонку, но это не то, что мне нужно. Я хочу получить объявление, по которому звонили перед самым визитом.

Единственное, до чего я сам допёр - это ограничить поиск 30 днями по формуле:
=ИНДЕКС(Таблица1[Рекламное объявление];ПОИСКПОЗ(1;(Таблица1[Номер телефона]=[@[Номер телефона]])*(([@[Дата визита]]-Таблица1[Дата звонка])<30);0))
это лучше, чем было, но всё равно не то, что хочу.

Представляется какая-то вот такая формула:
=ИНДЕКС(Таблица1[Рекламное объявление];ПОИСКПОЗ(1;(Таблица1[Номер телефона]=[@[Номер телефона]])*(НАИМЕНЬШИЙ([@[Дата визита]]-Таблица1[Дата звонка];1));0)), но выдаёт ошибку

На всякий случай: про Ctrl Shift Enter я знаю.
Шлюхогон42
 
Перейти    
Почему пусто больше нуля?, По непонятной причине "пусто" больше нуля
 
#
14.01.2021 20:42:49
Добрый день!
Помогите разобраться. Использовал формулу: =ЕСЛИОШИБКА(F13/H13;"") Далее воспользовался условным форматированием, условие "больше 0" И у меня выделяются все ячейки, где получилось пусто. Как такое могло произойти? Ведь пусто - это эквивалент 0
Шлюхогон42
 
Перейти    
Страницы: 1
Наверх