Power query как посчитать счетесли
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
COUNTROWS
Функция COUNTROWS подсчитывает количество строк в указанной таблице или в таблице, определенной выражением.
Синтаксис
COUNTROWS([]) Параметры
| Термин | Определение |
|---|---|
| table | (Необязательно) Имя таблицы, содержащей строки для подсчета, или выражение, возвращающее таблицу. Если он не указан, значение по умолчанию является домашней таблицей текущего выражения. |
Возвращаемое значение
Замечания
- Эту функцию можно использовать для подсчета количества строк в базовой таблице, но чаще используется для подсчета количества строк, которые приводят к фильтрации таблицы или применения контекста к таблице.
- Всякий раз, когда нет строк для статистической обработки, функция возвращает пустое значение. Однако если есть строки, но ни одна из них не соответствует указанным критериям, функция возвращает значение 0. Microsoft Excel также возвращает ноль, если строки не найдены в соответствии с условиями.
- Дополнительные сведения о рекомендациях при использовании COUNT и COUNTROWS см. в статье «Использование COUNTROWS» вместо COUNT в DAX.
- Эта функция не поддерживается для использования в режиме DirectQuery при использовании в вычисляемых столбцах или правилах безопасности на уровне строк (RLS).
Пример 1
В следующем примере показано, как подсчитать количество строк в таблице Orders. Ожидаемый результат — 52761.
= COUNTROWS('Orders') Пример 2
В следующем примере показано, как использовать COUNTROWS с контекстом строки. В этом сценарии существует два набора данных, связанных по номеру заказа. Торговый посредник таблицы содержит одну строку для каждого торгового посредника; Таблица ResellerSales содержит несколько строк для каждого заказа, каждая строка, содержащая один заказ для определенного торгового посредника. Таблицы связаны связью в столбце Торговых посредников.
Формула получает значение ResellerKey, а затем подсчитывает количество строк в связанной таблице с одинаковым идентификатором торгового посредника. Результат выводится в столбце CalculatedColumn1 .
= COUNTROWS(RELATEDTABLE(ResellerSales)) В следующей таблице показана часть ожидаемых результатов:
| ResellerKey | CalculatedColumn1 |
|---|---|
| 1 | 73 |
| 2 | 70 |
| 3 | 394 |
Power Query Базовый №17. Группировка
В этом уроке мы научимся выполнять группировку в Power Query. Группировка — это операция, при которой вы находите уникальные значения в одном поле и выполняете агрегирования каких-то других полей. Например, в этом уроке мы найдем сумму продаж на каждый день, а еще посчитаем количество сделок на каждый день, найдем максимальную сделку на каждый день и выполним другие агрегирования.
Группировку можно также выполнять и по нескольким полям. В таком случае мы получим агрегаты для каждого уникального сочетания этих нескольких полей.
Группировка в Power Query — это аналог операции GROUP BY из SQL. Так же это напоминает функции СУММЕСЛИ/СУММЕСЛИМН, СЧЕТЕСЛИ/СЧЕТЕСЛИМН, СРЗНАЧЕСЛИ и другие подобные функции. Если вы искали аналоги этих функций в Power Query, то вам нужно воспользоваться группировкой.
Решение
Задачу можно решить используя только лишь пользовательский интерфейс Power Query.
Нужно выбрать столбец, по которому мы будем делать группировку, перейти на вкладку Главная — Группировать по.
Далее нужно выбрать столбцы для агрегирования и вид агрегирования.
Примененные функции
- Table.RemoveColumns
- Table.Skip
- Table.PromoteHeaders
- Table.SelectRows
- Table.TransformColumns
- Text.Start
- Text.From
- Table.TransformColumnTypes
- Int64.Type
- Table.Group
- List.Sum
- Table.Sort
- Order.Ascending
- Table.AddColumn
Код
let source = Excel.Workbook(File.Contents(Путь), null, true), get_table = source<[Name = "Источник"]>[Data], cols_select = Table.RemoveColumns(get_table, ), rows_skip = Table.Skip(cols_select, 10), tab_promote_headers = Table.PromoteHeaders( rows_skip, [PromoteAllScalars = true] ), rows_skip_2 = Table.Skip(tab_promote_headers, 1), rows_select = Table.SelectRows(rows_skip_2, each ([Время] <> null)), col_text_start = Table.TransformColumns( rows_select, > ), types = Table.TransformColumnTypes( col_text_start, < , , , , , > ), tab_group = Table.Group( types, , < , , > ), tab_sort = Table.Sort(tab_group, >), tab_add_col_profit = Table.AddColumn( tab_sort, "Прибыль", each [Сумма] - [Себестоимость], type number ), value_sum = List.Sum(tab_add_col_profit[Прибыль]), tab_link = tab_add_col_profit, #"tab_add_col_%" = Table.AddColumn( tab_link, "Доля от общей прибыли", each [Прибыль] / value_sum, Percentage.Type ) in #"tab_add_col_%"Этот урок входит в Базовый курс Power Query
| Номер урока | Урок | Описание |
|---|---|---|
| 1 | Зачем нужен Power Query. Обзор возможностей | Этот урок сам по себе является мини-курсом. Здесь вы узнаете для каких видов операций с данными создан Power Query. |
| 2 | Подключение Excel | Подключаемся к файлам Excel. Импортируем данные из таблиц, именных диапазонов, динамических именных диапазонов. |
| 3 | Подключение CSV/TXT, таблиц, диапазонов | Подключаемся к к файлам CSV/TXT, Excel. |
| 4 | Объединить таблицы по вертикали | Учимся объединять две таблицы по вертикали — combine. |
| 5 | Объединить по вертикали все таблицы одной книги друг за другом | Как объединить по вертикали все таблицы одной книги, находящиеся на разных листах Excel. |
| 6 | Объединить по вертикали все файлы в папке | Объединяем по вертикали таблицы, которые находятся в разных файлах в одной папке. |
| 7 | Объединение таблиц по горизонтали | Учимся объединять таблицы по горизонтали — JOIN, merge. |
| 8 | Объединить таблицы с агрегированием | Объединить таблицы по горизонтали и сразу выполнить группировку с агрегированием — JOIN + GROUP BY. |
| 9 | Анпивот (Unpivot) | Изучаем операцию Анпивот — из сводной таблицы делаем таблицу с данными. |
| 10 | Многоуровневый анпивот (Анпивот с подкатегориями) | Более сложный вариант Анпивота — в строках находится несколько измерений. |
| 11 | Скученные данные | Данные собраны в одном столбце, нужно правильно его разбить на несколько. |
| 12 | Скученные данные 2 | Разбираем еще один пример скученных данных. |
| 13 | Ссылка на другую строку | Как сослаться на другую строку. |
| 14 | Ссылка на другую строку 2 | Как сослаться на другую строку, используя объединение по горизонтали. |
| 15 | Виды объединения таблиц по горизонтали | Изучаем виды объединения таблиц по горизонтали — LEFT JOIN, FULL JOIN, INNER JOIN, CROSS JOIN. |
| 16 | Виды объединения таблиц по горизонтали 2 | Изучаем анти-соединение и соединение таблицы с ней же самой — ANTI JOIN, SELF JOIN. |
| 17 | Группировка | Изучаем операцию группировки с агрегированием — GROUP BY. |
| 18 | Консолидация множества таблиц пользовательской функцией | Объединяем по вертикали множество таблиц с предварительной обработкой при помощи пользовательской функции. |
| 19 | Деление на справочник и факт | Разделим один датасет на два датасета: справочник и факт. |
| 20 | Создание параметра | Мы можем ввести значение в какую-то ячейку Excel, а потом передать это значение в формулу Power Query. |
| 21 | Таблица параметров | Создадим целую таблицу параметров и будем их использовать в запросах Power Query. |
| 22 | Объединение таблиц по вертикали, когда не совпадают заголовки столбцов | Как объединить две таблицы по вертикали, если названия столбцов не совпадают. |
| 23 | Поиск ключевых слов | Научимся искать ключевые слова в текстовом поле. |
| 24 | Поиск ключевых слов 2 | Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию. |
CALCULATE и CALCULATETABLE в Power BI и Power Pivot — самые важные функции языка DAX
до конца распродажи осталось:
» CALCULATE и CALCULATETABLE в Power BI и Power Pivot — самые важные функции языка DAX
Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):
- DAX функция CALCULATE
- Пример использования функции CALCULATE
- DAX функция CALCULATETABLE
Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы разберем, пожалуй, самые важные и главные функции в языке DAX — это CALCULATE и CALCULATETABLE. Эти функции используются практически во всех формулах и вычислениях, которые мы составляем в Power BI или Excel (Powerpiwot).
А главные они потому, что это единственные функции в DAX, которые способны не то чтобы накладывать на выражения те или иные фильтры, так и полностью заменять или удалять предыдущие наложенные фильтры.
По всем свойствам обе функции CALCULATE и CALCULATETABLE полностью идентичны, и различаются лишь тем, что CALCULATE — работает с выражениями, возвращающими одно конкретное скалярное значение, а CALCULATETABLE — с табличными выражениями. Но, обо всем по порядку.
Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».
Да, и еще один момент, до 24 ноября 2023 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 60% (вместо 10000, всего за 4000 руб.)
В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.
Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 24 ноября 2023 г.): узнать подробнее
до конца распродажи осталось:
DAX функция CALCULATE в Power BI и Power Pivot
CALCULATE () — вычисляет выражение, измененное внутренними фильтрами.
Синтаксис: CALCULATE (Выражение; Фильтр 1; Фильтр 2; …; Фильтр N), где:
- Выражение — то выражение, которое нужно вычислить (обязательный параметр для CALCULATE, без него эта функция работать не будет)
- Фильтр — условия фильтров (необязательный параметр, количество фильтров может быть от 0 до многих и все они сочетаются в режиме «и»)
Фильтры в CALCULATE не могут ссылаться на различные меры или вложенные функции CALCULATE. При этом, в условиях фильтров могут использоваться какие-либо другие функции DAX, вычисляющие одно скалярное значение или создающие запрос уточнения одного скалярного значения.
Если говорить проще про функцию CALCULATE, то она при помощи своих условий фильтров способна дополнить, заменить или полностью удалить все предыдущие наложенные фильтры на выражение, указанное в первом параметре.
Пример использования функции CALCULATE
Для понимания функционирования и работы DAX формул на основе CALCULATE, разберем примеры ее работы в программе Power BI Desktop.
Имеется исходная таблица «Общие Продажи»:
Задача — вычислить сумму продаж только по первому отделу. Создадим в Power BI Desktop во вкладке «Моделирование» меру по следующему коду формулы с участием функций SUM и CALCULATE:
Сумма Продаж Отдел 1 = CALCULATE( SUM('ОбщиеПродажи'[Продажи]); 'ОбщиеПродажи'[Отдел] = "Первый отдел" )
Где, SUM вычислит сумму всех продаж, находясь под созданным функцией CALCULATE фильтром из второго параметра.
Результатом выполнения этого кода формулы будет сумма продаж по первому отделу, равная 49000:
Причем созданный функцией CALCULATE фильтр, будет заменять любой другой фильтр, который может наложить пользователь во вкладке «Отчеты» в Power BI.
В этом можно убедиться на примере ниже, где на срезе «Отделы» установлен пользовательский фильтр «Второй отдел», но CALCULATE полностью заменяет его своим условием и формула высчитывает сумму опять же только по первому отделу, то есть сумма продаж равна 49000:
В качестве выражения в первом параметре CALCULATE можно рассчитывать очень многие формулы и вставлять туда какие-либо другие функции DAX, которые Вам понадобятся в конкретной ситуации.
Также, при этом, создавая хитрые переплетения фильтров из второго и последующих параметров CALCULATE, можно формировать очень сложные формулы и необычные вычисления в Power BI и Power Pivot.
В рамках этой ознакомительной статьи сложные примеры мы пока рассматривать не будем, но для закрепления материала, посмотрим еще один небольшой пример формулы, несколько сложнее предыдущего.
Рассчитаем при помощи функции COUNT количество менеджеров, у которых сумма продаж больше 20000. При условии, что менеджеры не должны принадлежать первому отделу. Условия в этой формуле мы зададим при помощи фильтров CALCULATE:
Количество Менеджеров = CALCULATE ( COUNT ('ОбщиеПродажи'[Продажи]); 'ОбщиеПродажи'[Отдел] <> "Первый отдел"; 'ОбщиеПродажи'[Продажи] > 20000 )
В этой формуле COUNT считает количество менеджеров, находясь под действием сразу двух фильтров от CALCULATE, где столбец [Отдел] неравен значению «Первый отдел» и одновременно с этим в столбце [Продажи] все значения должны быть более 20000. Как итог, формула рассчитает количество менеджеров, равное 2:
Теперь давайте рассмотрим собрата CALCULATE — функцию CALCULATETABLE, которая, как я писал в самом начале по всем своим свойствам полностью аналогична первой функции и различия между ними в том, что первая функция работает с единичными скалярными значениями, а вторая — с табличными выражениями.
DAX функция CALCULATETABLE в Power BI и Power Pivot
CALCULATETABLE () — вычисляет табличное выражение, измененное внутренними фильтрами.
Синтаксис: CALCULATETABLE (Табличное выражение; Фильтр 1; Фильтр 2; …; Фильтр N), где:
- Табличное выражение — то табличное выражение, которое нужно вычислить (обязательный параметр для CALCULATETABLE, без него эта функция работать не будет)
- Фильтр — условия фильтров (необязательный параметр, количество фильтров может быть от 0 до многих и все они сочетаются в режиме «и»)
Функцию CALCULATETABLE нельзя путать с функцией фильтра таблиц в DAX — FILTER. Эта функция просто фильтрует таблицы по заданным условиям фильтра. А CALCULATETABLE, в свою очередь, не просто фильтрует, а заменяет или удаляет фильтры, что в итоге дает совершенно другой результат.
Рассмотрим небольшой пример формулы на основе CALCULATETABLE. В качестве исходной таблицы возьмем ту же таблицу по продажам менеджеров, которую брали в примерах выше:
В этом примере также рассчитаем сумму продаж, но только по второму отделу. В данном случае фильтры мы будем устанавливать функцией CALCULATETABLE, а сумму рассчитаем при помощи второй функции суммирования в DAX — SUMX (подробный разбор функции SUMX Вы можете прочитать в этой статье):
Сумма Продаж Отдел 2 = SUMX ( CALCULATETABLE ( 'ОбщиеПродажи'; 'ОбщиеПродажи'[Отдел] = "Второй отдел" ); 'ОбщиеПродажи'[Продажи] )
В данной формуле CALCULATETABLE возвращает таблицу, отфильтрованную по условию «Второй отдел», причем этот фильтр будет заменять любой другой пользовательский фильтр, наложенный на этот же столбец [Отдел]. А SUMX рассчитает по этой отфильтрованной таблице сумму по столбцу [Продажи]. В итоге, получится сумма продаж именно по второму отделу, равная 40500:
На этом, с разбором CALCULATE и CALCULATETABLE в Power BI и Power Pivot, в данной статье все.
Также, Вы можете изучить в этой статье формулу расчета в DAX количества новых, постоянных и потерянных клиентов с участием функций CALCULATE и CALCULATETABLE.
Помните, что BI аналитика, Power BI и язык функций и формул DAX, в частности, это действительно просто. Главное, разобраться во всем и не просто прочитать статью, но и повторить все самостоятельно на практике. Тогда результат не заставит Вас долго себя ждать!
Также, напоминаю Вам, что до 24 ноября 2023 г. у Вас имеется шикарная возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 60% (вместо 10000, всего за 4000 руб.)
В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.
Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 60% (до 24 ноября 2023 г.): узнать подробнее
До конца распродажи осталось:
Пожалуйста, оцените статью:
- 5
- 4
- 3
- 2
- 1
![[Экспресс-видеокурс] Быстрый старт в языке DAX](https://biprosto.ru/wp-content/uploads/2018/08/kurs-free-5.png)
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.
Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D
до конца распродажи осталось:
Что еще посмотреть / почитать?
RANKX — функция ранжирования (ранга) в DAX (Power BI и Power Pivot)
Как провести конкатенацию строк (объединить текст) в Power BI или Power Pivot? DAX функции CONCATENATE и CONCATENATEX
DAX функции YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot