Power Query и DAX: ИИ-нейросеть для Excel и Power BI онлайн

Это должна быть мера, а не вычисляемый столбец и не вычисляемая таблица.

Почему:

• мера пересчитывается в текущем фильтр-контексте визуала;
• вам нужна доля текущей строки визуала от общего итога по всем товарам;
• при этом должен сохраниться фильтр по году и другим внешним срезам, но нужно убрать только фильтр по товару.

Доля продаж :=
VAR SalesCurrentRow =
    [Сумма]
VAR SalesAllProductsInCurrentFilters =
    CALCULATE (
        [Сумма],
        REMOVEFILTERS ( 'Товары'[Товар] )
    )
RETURN
    DIVIDE ( SalesCurrentRow, SalesAllProductsInCurrentFilters )

Что делает код

• VAR SalesCurrentRow = [Сумма]
  Берёт продажи в текущем контексте строки визуала.
  Если визуал построен по 'Товары'[Товар] или 'Товары'[Категория], то здесь будет сумма именно для этой строки.
• CALCULATE (...)
  Меняет контекст фильтра для расчёта знаменателя.
• REMOVEFILTERS ( 'Товары'[Товар] )
  Снимает фильтр только со столбца 'Товары'[Товар], то есть убирает влияние текущего товара строки визуала.
  При этом фильтр по году сохраняется, если он приходит через дату или другой внешний срез, потому что мы его не убираем.
• DIVIDE (...)
  Безопасно делит продажи строки на общий итог по товарам.
  Если знаменатель равен нулю, вернёт BLANK() вместо ошибки.

Важное замечание про время

У вас нет отдельной таблицы дат.
Для логики времени и корректного анализа по году в Power BI лучше создать отдельную таблицу дат и связать её с 'Продажи'[Дата].

Пример календаря:

Календарь =
CALENDAR ( MIN ( 'Продажи'[Дата] ), MAX ( 'Продажи'[Дата] ) )

Потом в этой таблице можно добавить столбец года:

Год = YEAR ( 'Календарь'[Date] )

И использовать 'Календарь'[Год] в срезах и фильтрах.
Тогда ваша мера будет корректно уважать фильтр по году.

Если фильтр по году уже идёт через таблицу дат

Если у вас будет отдельная таблица 'Календарь', и вы будете фильтровать по году из неё, то мера выше уже будет работать правильно, если связь активна между 'Календарь'[Date] и 'Продажи'[Дата].

Проверка на маленьких данных

Данные-заглушки

Товары

Продажи

Ожидаемое поведение

Если на отчёте выбран год 2024, то:

• для товара A:
  числитель = 100
  знаменатель = 100 + 300 + 600 = 1000
  результат = 0,1 или 10%
• для товара B:
  300 / 1000 = 30%
• для товара C:
  600 / 1000 = 60%
• в строке Итог по всем товарам:
  числитель = 1000
  знаменатель = 1000
  результат = 100%

Если выбран год 2025, то аналогично:

• A = 50 / 500 = 10%
• B = 150 / 500 = 30%
• C = 300 / 500 = 60%

Почему не вычисляемый столбец

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

• по выбранному году,
• по текущему срезу,
• по строке/итогу визуала.

Поэтому здесь нужна именно мера.

Если хотите, я сразу могу дать ещё и вариант меры, где доля считается от итога по категории, а не по всем товарам.

Вот это должны быть меры, а не вычисляемый столбец и не вычисляемая таблица.

Почему:

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

Ниже готовые меры для Power BI Desktop.

Выручка =
SUM ( 'Продажи'[Сумма] )

Выручка с начала года =
VAR CurrentYTD =
    CALCULATE (
        [Выручка],
        DATESYTD ( 'Календарь'[Дата] )
    )
RETURN
    CurrentYTD

Выручка прошлого года за тот же период =
VAR CurrentYTD =
    DATESYTD ( 'Календарь'[Дата] )
VAR PreviousYearSamePeriod =
    SAMEPERIODLASTYEAR ( CurrentYTD )
VAR Result =
    CALCULATE (
        [Выручка],
        PreviousYearSamePeriod
    )
RETURN
    Result

Как это работает

1) [Выручка]

SUM ( 'Продажи'[Сумма] )

Просто суммирует продажи в текущем фильтр-контексте.

2) [Выручка с начала года]

CALCULATE (
    [Выручка],
    DATESYTD ( 'Календарь'[Дата] )
)

Что делает CALCULATE:

• берёт текущий фильтр-контекст визуала;
• модифицирует его: добавляет фильтр по датам от начала года до текущей даты;
• затем пересчитывает [Выручка] уже в этом изменённом контексте.

Что снимается/меняется:

• DATESYTD('Календарь'[Дата]) задаёт временной фильтр на таблице дат;
• внешний контекст месяца на графике не исчезает полностью, а становится основой для вычисления YTD внутри каждого месяца.

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

3) [Выручка прошлого года за тот же период]

VAR CurrentYTD =
    DATESYTD ( 'Календарь'[Дата] )
VAR PreviousYearSamePeriod =
    SAMEPERIODLASTYEAR ( CurrentYTD )
RETURN
    CALCULATE (
        [Выручка],
        PreviousYearSamePeriod
    )

Идея:

• сначала определяется текущий YTD-период;
• потом SAMEPERIODLASTYEAR сдвигает этот период на 1 год назад;
• затем CALCULATE считает [Выручка] уже по датам прошлого года.

Контекст фильтра:

• на визуале по месяцам каждая точка имеет свой текущий месяц;
• DATESYTD формирует диапазон с начала года до этого месяца;
• SAMEPERIODLASTYEAR переносит этот диапазон в прошлый год;
• CALCULATE подменяет фильтр по 'Календарь'[Дата] на этот сдвинутый период.

Важно про фильтры

Вы просили, чтобы фильтр снимался со столбца таблицы-справочника.
В этой модели справочник времени — это 'Календарь', и именно её столбец 'Календарь'[Дата] участвует в time intelligence.
Для таких задач не нужно вручную использовать REMOVEFILTERS, потому что DATESYTD и SAMEPERIODLASTYEAR работают через календарную таблицу и сами задают нужный временной контекст.

Для графика по месяцам

Используйте:

• ось: 'Календарь'[Месяц]
  или лучше, если есть сортировка по номеру месяца в модели;
• значения:
  • [Выручка с начала года]
  • [Выручка прошлого года за тот же период]

Если нужно сравнение именно по месяцам, а не накопительно, тогда вместо YTD нужны другие меры — но по вашему описанию нужен именно накопительный сравнимый ряд.

Проверка на маленьких данных

Таблица Продажи

Таблица Календарь

Ожидаемый результат

Для 2024 года:

• Январь
  • [Выручка с начала года] = 100
  • [Выручка прошлого года за тот же период] = 80
• Февраль
  • [Выручка с начала года] = 250
  • [Выручка прошлого года за тот же период] = 200
• Март
  • [Выручка с начала года] = 450
  • [Выручка прошлого года за тот же период] = 360

Если нужен процент сравнения

Можно добавить ещё одну меру:

Рост Выручки YTD % =
VAR CurrentValue =
    [Выручка с начала года]
VAR PreviousValue =
    [Выручка прошлого года за тот же период]
RETURN
    DIVIDE ( CurrentValue - PreviousValue, PreviousValue )

DIVIDE здесь лучше обычного деления, потому что корректно обрабатывает деление на 0.

Если хотите, могу сразу дать вариант с мерой разницы и % роста для той же визуализации.

let
    // Источник: папка с файлами
    Source = Folder.Files("[ЗАПОЛНИТЬ: путь к папке с файлами]"),

    // Оставляем только нужные файлы, при необходимости можно сузить по расширению
    FilteredFiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),

    // Добавляем содержимое каждого файла
    AddFileContent = Table.AddColumn(FilteredFiles, "FileData", each Excel.Workbook([Content], true)),

    // Разворачиваем структуру книги
    ExpandWorkbook = Table.ExpandTableColumn(
        AddFileContent,
        "FileData",
        {"Name", "Data", "Kind"},
        {"Name", "Data", "Kind"}
    ),

    // Оставляем только лист "Выгрузка"
    KeepWorksheet = Table.SelectRows(ExpandWorkbook, each [Kind] = "Sheet" and [Name] = "Выгрузка"),

    // Добавляем имя файла
    AddFileName = Table.AddColumn(KeepWorksheet, "Имя файла", each [Name], type text),

    // Берём данные листа
    KeepData = Table.SelectColumns(AddFileName, {"Имя файла", "Data"}),

    // Разворачиваем таблицу листа
    ExpandData = Table.ExpandTableColumn(
        KeepData,
        "Data",
        {"Дата", "Клиент", "Сумма", "Менеджер"},
        {"Дата", "Клиент", "Сумма", "Менеджер"}
    ),

    // Убираем пустые строки
    RemoveBlankRows = Table.SelectRows(
        ExpandData,
        each not (
            ([Дата] = null or Text.Trim(Text.From([Дата])) = "") and
            ([Клиент] = null or Text.Trim(Text.From([Клиент])) = "") and
            ([Сумма] = null or Text.Trim(Text.From([Сумма])) = "") and
            ([Менеджер] = null or Text.Trim(Text.From([Менеджер])) = "")
        )
    ),

    // Убираем строки "Итого"
    RemoveTotalRows = Table.SelectRows(
        RemoveBlankRows,
        each not (
            Text.Upper(Text.Trim(Text.From([Клиент]))) = "ИТОГО" or
            Text.Upper(Text.Trim(Text.From([Менеджер]))) = "ИТОГО" or
            Text.Upper(Text.Trim(Text.From([Дата]))) = "ИТОГО" or
            Text.Upper(Text.Trim(Text.From([Сумма]))) = "ИТОГО"
        )
    ),

    // Приводим типы столбцов явно
    ChangeTypes = Table.TransformColumnTypes(
        RemoveTotalRows,
        {
            {"Дата", type date},
            {"Клиент", type text},
            {"Сумма", type number},
            {"Менеджер", type text},
            {"Имя файла", type text}
        },
        "ru-RU"
    )
in
    ChangeTypes

Что делает каждый шаг:

• Source — подключается к папке с файлами.
• FilteredFiles — убирает скрытые файлы.
• AddFileContent — открывает каждый файл как книгу Excel.
• ExpandWorkbook — разворачивает список объектов книги.
• KeepWorksheet — оставляет только лист Выгрузка.
• AddFileName — добавляет имя файла в отдельный столбец.
• KeepData — оставляет только имя файла и данные листа.
• ExpandData — разворачивает столбцы листа в общую таблицу.
• RemoveBlankRows — удаляет полностью пустые строки.
• RemoveTotalRows — удаляет строки, где встречается Итого.
• ChangeTypes — задаёт типы столбцов явно с культурой ru-RU для корректного чтения чисел с запятой.

Куда вставить код:

• В Excel откройте Данные → Получить данные → Из файла → Из папки.
• Затем откройте Расширенный редактор в Power Query.
• Замените содержимое запроса этим кодом целиком.

Проверка на маленьком примере:

let
    Sample = #table(
        {"Имя файла", "Дата", "Клиент", "Сумма", "Менеджер"},
        {
            {"Файл1.xlsx", #date(2024, 1, 10), "ООО Альфа", 1500, "Иванов"},
            {"Файл1.xlsx", null, null, null, null},
            {"Файл2.xlsx", #date(2024, 1, 11), "Итого", 3000, "Петров"},
            {"Файл2.xlsx", #date(2024, 1, 12), "ООО Бета", 2500, "Петров"}
        }
    ),
    RemoveBlankRows = Table.SelectRows(
        Sample,
        each not (
            ([Дата] = null or Text.Trim(Text.From([Дата])) = "") and
            ([Клиент] = null or Text.Trim(Text.From([Клиент])) = "") and
            ([Сумма] = null or Text.Trim(Text.From([Сумма])) = "") and
            ([Менеджер] = null or Text.Trim(Text.From([Менеджер])) = "")
        )
    ),
    RemoveTotalRows = Table.SelectRows(
        RemoveBlankRows,
        each not (
            Text.Upper(Text.Trim(Text.From([Клиент]))) = "ИТОГО" or
            Text.Upper(Text.Trim(Text.From([Менеджер]))) = "ИТОГО" or
            Text.Upper(Text.Trim(Text.From([Дата]))) = "ИТОГО" or
            Text.Upper(Text.Trim(Text.From([Сумма]))) = "ИТОГО"
        )
    )
in
    RemoveTotalRows

Ожидаемый результат проверки:

• останутся строки:
  • Файл1.xlsx | 2024-01-10 | ООО Альфа | 1500 | Иванов
  • Файл2.xlsx | 2024-01-12 | ООО Бета | 2500 | Петров

Если в ваших файлах имя листа иногда отличается, нужно будет заменить фильтр KeepWorksheet на [ЗАПОЛНИТЬ: точное имя листа или правило отбора].

Причина ошибки простыми словами:

Ваш код считает:
- в числителе: общую сумму всех строк продаж;
- в знаменателе: количество строк, где есть 'Продажи'[НомерЧека].

Но у вас в одном чеке бывает несколько строк.
Значит, 'Продажи'[НомерЧека] повторяется много раз, и COUNT(Продажи[НомерЧека]) считает строки, а не уникальные чеки.

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

Что должно быть:
- средний чек = общая выручка / количество уникальных чеков;
- для этого нужен DISTINCTCOUNT по 'Продажи'[НомерЧека].

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

Исправленный код целиком:

Средний чек :=
VAR TotalSales =
    SUM ( 'Продажи'[Сумма] )
VAR TotalReceipts =
    DISTINCTCOUNT ( 'Продажи'[НомерЧека] )
RETURN
    DIVIDE ( TotalSales, TotalReceipts )

Как это работает в контексте фильтра:
- SUM ( 'Продажи'[Сумма] ) считает сумму только по тем строкам, которые попали в текущий фильтр отчёта: например, по выбранному товару, периоду, магазину или любому другому фильтру модели;
- DISTINCTCOUNT ( 'Продажи'[НомерЧека] ) считает количество уникальных чеков в том же самом текущем контексте фильтра;
- DIVIDE безопасно делит сумму на число чеков и не падает на делении на ноль;
- здесь CALCULATE не нужен, потому что вы не меняете фильтры вручную. Если бы понадобилось снять фильтр, тогда использовали бы CALCULATE и, например, ALL('Продажи') или REMOVEFILTERS('Продажи'[НомерЧека]), но в этой задаче это лишнее и опасно: так можно случайно убрать нужные фильтры и исказить средний чек.

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

Проверка на маленьком примере:

Продажи:
- НомерЧека = 1, Товар = A, Сумма = 100
- НомерЧека = 1, Товар = B, Сумма = 50
- НомерЧека = 2, Товар = C, Сумма = 200
- НомерЧека = 3, Товар = A, Сумма = 150
- НомерЧека = 3, Товар = D, Сумма = 50

Что вернёт мера:
- SUM('Продажи'[Сумма]) = 550
- DISTINCTCOUNT('Продажи'[НомерЧека]) = 3
- Средний чек = 550 / 3 = 183,33

Если бы оставить ваш вариант:
- COUNT('Продажи'[НомерЧека]) = 5
- 550 / 5 = 110
Это и есть причина занижения.

Важно:
- если 'Продажи'[НомерЧека] может быть пустым, DISTINCTCOUNT не посчитает blank как обычный чек;
- если номер чека хранится как текст, DISTINCTCOUNT тоже работает корректно.

Если хотите, можно ещё сделать отдельную меру для количества чеков:
[Количество чеков] :=
DISTINCTCOUNT ( 'Продажи'[НомерЧека] )

И тогда средний чек можно записать так:
Средний чек :=
DIVIDE ( [Выручка], [Количество чеков] )

Но только если у вас уже есть мера [Выручка]. Если нет — используйте первый вариант целиком.

Причина ошибки простая: Power Query пытается прочитать Сумма как число по умолчанию, а в CSV у вас десятичный разделитель — запятая. В такой ситуации нужен явный перевод типа с культурой "ru-RU", иначе возникает DataFormat.Error.

Исправленный код целиком:

let
    Источник = Csv.Document(
        File.Contents("C:\data\sales.csv"),
        [Delimiter=";", Encoding=65001, QuoteStyle=QuoteStyle.Csv]
    ),
    Заголовки = Table.PromoteHeaders(Источник, [PromoteAllScalars=true]),
    Типы = Table.TransformColumnTypes(
        Заголовки,
        {{"Дата", type date}, {"Сумма", type number}},
        "ru-RU"
    )
in
    Типы

Что делает каждый шаг:

• Источник — читает CSV-файл sales.csv с разделителем ; и кодировкой UTF-8.
• Заголовки — поднимает первую строку в заголовки столбцов.
• Типы — явно задаёт типы столбцов: Дата как date, Сумма как number, с культурой "ru-RU" для чисел с запятой.

Куда вставить код:

• В Power BI Desktop откройте Power Query Editor → Расширенный редактор → замените содержимое запроса этим кодом целиком.

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

Это мера DAX для Power BI Desktop:

<pre>Клиенты с покупкой =
CALCULATE(
    DISTINCTCOUNT('Продажи'[Клиент]),
    FILTER(ALL('Календарь'), 'Календарь'[Дата] &lt;= MAX('Календарь'[Дата]))
)</pre>

Что она делает простыми словами:

1. Берёт таблицу <code>'Продажи'</code>.
2. Считает, сколько разных клиентов есть в столбце <code>'Продажи'[Клиент]</code>.
3. Но считает не по всей таблице сразу, а в контексте дат до текущей даты в визуале.
4. Для этого снимает фильтры с таблицы <code>'Календарь'</code> и затем оставляет только даты, которые меньше или равны текущей максимальной дате.

По шагам:

1. <code>CALCULATE(...)</code>
   - Меняет контекст фильтра.
   - То есть берет исходный набор строк и пересчитывает выражение уже с новыми фильтрами.

2. <code>DISTINCTCOUNT('Продажи'[Клиент])</code>
   - Считает количество уникальных клиентов.
   - Если один и тот же клиент встречается много раз в продажах, он всё равно будет посчитан один раз.

3. <code>FILTER(ALL('Календарь'), ...)</code>
   - <code>ALL('Календарь')</code> снимает все фильтры с таблицы <code>'Календарь'</code>.
   - Потом <code>FILTER</code> оставляет только те строки календаря, где дата меньше или равна текущей максимальной дате.

4. <code>'Календарь'[Дата] &lt;= MAX('Календарь'[Дата])</code>
   - <code>MAX('Календарь'[Дата])</code> берёт последнюю дату в текущем контексте визуала.
   - Например, если строка отчёта — месяц, то это будет последняя дата месяца.
   - Затем берутся все даты календаря от начала до этой даты.

Что получится в результате:

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

Как работает контекст фильтра здесь:

- Визуал, срезы и фильтры страницы задают текущий контекст.
- <code>CALCULATE</code> заменяет часть этого контекста.
- <code>ALL('Календарь')</code> снимает фильтры именно с таблицы <code>'Календарь'</code>.
- Значит, если на отчёте выбран один месяц или один день, <code>ALL('Календарь')</code> убирает этот отбор, а затем <code>FILTER(... &lt;= MAX(...))</code> снова задаёт нужный диапазон дат.

Какие фильтры снимаются:

- Снимаются все фильтры с <code>'Календарь'</code> целиком, потому что используется <code>ALL('Календарь')</code>.
- Фильтры с таблицы <code>'Продажи'</code> напрямую не снимаются.
- Но если таблица <code>'Продажи'</code> связана с <code>'Календарь'</code>, то фильтр по датам на продажи влияет через связь, и этот эффект тут фактически переопределяется через календарь.

Где код может сломаться или дать неожиданный результат:

1. Если <code>'Календарь'</code> не является полноценной таблицей дат
   - Должны быть непрерывные даты без пропусков.
   - Желательно, чтобы таблица была помечена как таблица дат в Power BI.

2. Если связь между <code>'Календарь'</code> и <code>'Продажи'</code> неактивна или неправильная
   - Тогда фильтрация по датам может не попасть в продажи как ожидается.

3. Если в <code>'Продажи'[Клиент]</code> есть пустые значения
   - <code>DISTINCTCOUNT</code> может учитывать <code>BLANK()</code> как отдельное значение в зависимости от данных и контекста.

4. Если визуал уже отфильтровал календарь по другой логике
   - Например, по году, месяцу или дню.
   - <code>ALL('Календарь')</code> полностью убирает эти фильтры, что может быть не тем, что вы хотели.

5. Если вы хотели накопление только внутри года
   - Сейчас мера считает накопительно по всей доступной истории, а не только внутри текущего года.
   - Для накопления внутри года обычно используют <code>DATESYTD</code>, а не ручной фильтр через <code>ALL</code> и <code>MAX</code>.

Что важно про функции:

- <code>CALCULATE</code> — существует.
- <code>DISTINCTCOUNT</code> — существует.
- <code>FILTER</code> — существует.
- <code>ALL</code> — существует.
- <code>MAX</code> — существует.
- Порядок аргументов здесь корректный.

Если нужна более “правильная” time intelligence-версия, то для накопления с начала года лучше делать через отдельную таблицу дат и <code>DATESYTD</code>:

<pre>Клиенты с покупкой YTD =
CALCULATE(
    DISTINCTCOUNT('Продажи'[Клиент]),
    DATESYTD('Календарь'[Дата])
)</pre>

Проверка на маленьком примере:

Допустим, в <code>'Продажи'</code> есть:

- 01.01 — Клиент A
- 03.01 — Клиент B
- 05.01 — Клиент A
- 07.01 — Клиент C

Тогда мера даст:

- на 01.01 → 1
- на 03.01 → 2
- на 05.01 → 2
- на 07.01 → 3

Если хотите, я могу сразу переписать эту меру в более устойчивый вариант для YTD, MTD или накопления по всей истории без снятия фильтра с целой таблицы.