Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
DAX. Можно ли мерой посчитать количество непустых значений в строке сводной таблицы., PowerPivot
 
#1
09.08.2017 16:54:53
Всем здравствуйте.
Встал такой вопрос: можно ли создать меру, которая бы считала количество пустых ячеек в строке сводной таблицы, по аналогии с функцией СЧЁТ
В примере моделька и формулой сделан столбец, который хочу получить в виде меры.
Фактически нужно посчитать количество месяцев без продаж для каждой номенклатуры с учетом фильтров сводной таблицы.
Если это сильно заморочно, то хотя бы пните в какую сторону копать.
Изменено: PooHkrd - 09.08.2017 16:55:05
Вот горшок пустой, он предмет простой...
 
 
 
#2
09.08.2017 17:53:49
Доброе время суток.
Смотрю, коллега, вы до асимметричных сводных добрались? Задача не столь тривиальна. Через ISFILTERED, HASONEFILTER определяете, что вы находитесь в итогах. Лучше сделать дополнительный вычисляемый столбец в таблице, в котом для каждой даты вычисляете первый день месяца, тогда по DISTINCTCOUNT(Таблица[ПервыйДеньМесяца]) получите число месяцев продаж по точке. По CALCULATE(DISTINCTCOUNT(Таблица[ПервыйДеньМесяца]); ALL(Таблица)) получите сколько всего в таблице месяцев продаж. Осталось найти разницу.
Может быть засада, если одновременно у всех точек были месяцы без продаж, тогда можно воспользоваться DATEDIFF между крайними датами столбца ПервыйДеньМесяца.
Аналогично и по точкам месяца, только считаете разницу наименования всех точек таблицы и продававших в этом месяце.
Успехов.
Изменено: Андрей VG - 09.08.2017 20:04:58 (Поправил маленько, пока Зеленский не раскритиковал.)
 
 
 
#3
10.08.2017 06:57:53
PooHkrd, у вас в примере в колонке через функцию СЧЕТ посчитано количество месяцев, когда были продажи. Через DAX -
Месяцев с продажами:
Код
COUNTX(SUMMARIZE('Таблица1';[Дата (Месяц)];"sales";SUM('Таблица1'[Продано]));[sales])

Подсчет месяцев:
Код
CALCULATE(DISTINCTCOUNT([Дата (Месяц)]);ALLEXCEPT('Таблица1';'Таблица1'[Дата (Месяц)]))

Соответственно разница между этими двумя значениями будет количество месяцев без продаж.
Изменено: StepanWolkoff - 10.08.2017 06:58:51
 
 
 
#4
10.08.2017 09:47:16
Извиняюсь за ошибку в описании, действительно нужно было посчитать количество месяцев с продажами.
Спасибо всем огромное!
Вот горшок пустой, он предмет простой...
 
 
 
#5
10.08.2017 19:04:35
Пытаюсь разобраться в формуле:

Я правильно понял, что в подсчете месяцев CALCULATE вычисляет различные значения месяцев в столбце [Дата (Месяц)] функцией DISTINCTCOUNT, а через ALLEXCEPT вы убираете влияние столбца [Дата (Месяц)] на контекст строки?

А какой функцией убирать влияние на контекст фильтра? С этим вроде разобрался, это функция FILTER.

И еще не понятно: в справке MS указано, что DISTINCTCOUNT подсчитывает различные числовые значения в столбце,
Цитата
The DISTINCTCOUNT function counts the number of different cells in a column of numbers.
вы же ему скармливаете столбец с текстом. Это я что-то не так понимаю или MS что-то скрывает?
Изменено: PooHkrd - 11.08.2017 09:27:20
Вот горшок пустой, он предмет простой...
 
 
 
#6
14.08.2017 19:27:12
PooHkrd,
Цитата
PooHkrd написал:
ALLEXCEPT вы убираете влияние столбца [Дата (Месяц)] на контекст строки?
наоборот, ALLEXCEPT говорит о том, что надо убрать все фильтры, кроме указанного.
DISTINCTCOUNT - если честно, уже не помню. чтобы читал справку по этой функции. Но, если не изменяет память, то в первых версиях PowerPivot этой функции не было и использовали сочетание DISTINCT - COUNTROWS, потом добавили DISTINCTCOUNT и может тогда она считала только по числам, но не буду врать - не помню уже.
 
 
 
#7
15.08.2017 08:57:06
Спасибо.
Вот горшок пустой, он предмет простой...
 
 
 
#8
15.08.2017 13:14:40
Цитата
PooHkrd написал:
в справке MS указано, что DISTINCTCOUNT подсчитывает различные числовые значения в столбце,
Цитата
The DISTINCTCOUNT function counts the number of different cells in a column of numbers.
документация MS, этим многое сказано :)
F1 творит чудеса
 
 
 
#9
15.08.2017 13:29:19
А-а-а, это кривые ручки! Тогда ясно.
Вот горшок пустой, он предмет простой...
 
 
 
#10
11.04.2018 18:35:44
Добрый день,

Помогите, пожалуйста, решить задачу.

В Power Pivot для ячеек без значений необходимо выводить 0. Это должно быть реализовано именно с помощью DAX формул (а не с помощью "для пустых ячеек показывать 0), так как формула с результатом 0 будет частью более сложной формулы. Я пытаюсь это реализовать через countrows + 0, но, к сожалению, пока не удается. В приложенном файле желтым выделены ячейки, по которым необходимо отображать 0, а не пусто.

Это возможно сделать с помощью DAX?

Большое спасибо за помощь!
 
 
 
#11
11.04.2018 19:16:51
OksanaB, смотрю с телефона, поэтому не вижу модель. Попробуйте обернуть меру в функцию IF.
Код
IF(ISBLANK(SUM(Table[column1]);0; SUM(Table[column1]))

Как то так должно быть
 
 
 
#12
12.04.2018 11:15:17
StepanWolkoff, спасибо за помощь. К сожалению, if и isblank не помогают, 0 не выводится.
 
 
 
#13
12.04.2018 11:20:23
А докажите. Выложите пример ваших данных, и то что не работает.
Вот горшок пустой, он предмет простой...
 
 
 
#14
12.04.2018 11:58:44
PooHkrd, докажу, во вложении )

Что я делаю не так?

Помогите, пожалуйста, мне очень надо!
 
 
 
#15
12.04.2018 12:59:50
Впервые с таким сетом данных сталкиваюсь. Получается, что если в изначальной таблице нет строк подходящих под фильтры, которые задает сводная таблица, то РР в принципе не обсчитывает такие ячейки и не выдает для них результат, даже если написать формулу
Код
Мера:= 1
Поиск по буржуйским сайтам ничего не дал. Ждем корифеев.
Если все же предлагать хоть какие-то рабочие варианты, то можно тащить источник данных через PQ и добавлять в него недостающие строки с null в столбце [value]. Тогда уже +0 в формуле будет работать (но подозреваю что он даже и не будет нужен.
З.Ы. Есть подозрение что проблема решаема также путем добавления в модель данных справочника номенклатур и таблицы дат, связать все это дело. Тогда в качестве измерений в сводную нужно будет добавлять строки из справочника, столбцы из календаря, а меры рассчитывать уже из таблицы фактов.
Всегда так делаю, наверное потому и с такой проблемой ранее не сталкивался. Вообще добавление столбцов из таблицы фактов в качестве измерений в сводной - это, по моему опыту, всегда зло.
Изменено: PooHkrd - 12.04.2018 13:28:24
Вот горшок пустой, он предмет простой...
 
 
 
#16
12.04.2018 13:49:08
Цитата
PooHkrd написал:
Есть подозрение что проблема решаема также путем добавления в модель данных справочника номенклатур и таблицы дат, связать все это дело. Тогда в качестве измерений в сводную нужно будет добавлять строки из справочника, столбцы из календаря, а меры рассчитывать уже из таблицы фактов.
Все верно. Именно в таком сочетании будет работать IF()
Пояснение: Без справочников для модели просто не существует контекста 2016-продукт1, поэтому даже в сводной не просто пусто, а абсолютное ничто для меры, если так можно выразиться)))
Изменено: StepanWolkoff - 12.04.2018 13:53:42
 
 
 
#17
12.04.2018 13:52:33
Доброе время суток.
Offtop
Цитата
StepanWolkoff написал:
Все верно. Именно в таком сочетании будет работать IF()
Только пользователи DAX как огня боятся таблиц-измерений :)   А если столкнулись, то сразу же вопросы - а как в Excel сделать bideretional связь как в Power BI?
А проблема уже не раз обсуждалась и подобное решение проблемы предлагалось.
 
 
 
#18
12.04.2018 14:16:19
PooHkrd, StepanWolkoff, спасибо!

Модель данных из моего примера - это искусственно созданный маленький пример. На самом деле, реальный датасет огромный, с кучей измерений и фактов, источник - SQL процедура с сложным запросом из множества таблиц .  Получается, единственный выход - менять SQL код, добавляя измерения без фактов?

Знаю, что сложно без реального примера SQL запроса, но есть ли какие-то приемы добавления измерений без фактов в сложный SQL, например оперируя только результирующим select? Не хочется шерстить весь SQL запрос, меняя join-ы, запрос написан не мной, боюсь навредить.
 
 
 
#19
12.04.2018 14:23:07
Андрей VG, добрый день,
Подскажите, пожалуйста, ссылки где предлагалось реальное решение? Сама искала, не нашла (

Спасибо.
 
 
 
#20
12.04.2018 14:24:50
Ну так подключайтесь к SQL не через РР, а через PQ. Делаете 2 запроса - один формирует из таблицы фактов справочник, второй вытягивает собственно саму таблицу фактов. Календарь генерится средствами РР автоматически. Налаживайте связи и вперед.
Вот горшок пустой, он предмет простой...
 
 
 
#21
12.04.2018 14:32:00
OksanaB, предлагаю вам хорошо подумать, сформулировать свой вопрос и создать новую тему, а то как-то странно вы тут продолжили, вобще по другой теме.
Цитата
OksanaB написал:
так как формула с результатом 0 будет частью более сложной формулы.
из этого следует, что на самом деле вам нужно совсем другое нежели вы говорите в начале
Цитата
OksanaB написал:
В Power Pivot для ячеек без значений необходимо выводить 0.
Если вам нужно посчитать нули, то даже на вашей модели это можно сделать, если уж так хочется:
Код
Количество пустых:=var cj=SUMMARIZE(CROSSJOIN(VALUES('Таблица1'[product]);VALUES('Таблица1'[year]));[product];[year]; "summa";SUM('Таблица1'[value]))
return
COUNTROWS(FILTER(cj;ISBLANK([summa])))

Т.е. через CROSSJOIN получаем все возможные сочетания year-product, потом вычисляем для каждой строки value, и потом считаем пустые. НО!!!
Вы говорите
Цитата
OksanaB написал:
Модель данных из моего примера - это искусственно созданный маленький пример. На самом деле, реальный датасет огромный, с кучей измерений и фактов
А при таком подходе результат CROSSJOIN может оказаться очень большим и будет ооочень медленно считаться, а если не хватит ресурсов то и вобще не посчитать))
Так что еще раз предлагаю: хорошо подумайте, сформулируйте вопрос. создайте новую тему и приложите пример максимально близкий к тому что у вас в реальности.
 
 
 
#22
12.04.2018 14:41:46
Цитата
OksanaB написал:
Знаю, что сложно без реального примера SQL запроса, но есть ли какие-то приемы добавления измерений без фактов в сложный SQL, например оперируя только результирующим select? Не хочется шерстить весь SQL запрос, меняя join-ы, запрос написан не мной, боюсь навредить.
Зачем такие сложности? Пусть ваш мегасложный и мегакрутой запрос выгружает данные как и сейчас и это будет таблица фактов. Добавьте еще один запрос select * from таблица[products], если надо еще какое-то измерение, то еще один запрос с select к этому измерению. У вас в модель данные грузятся на прямую из SQL или сначала выгружаются в Excel или csv?
 
 
 
#23
12.04.2018 14:52:11
StepanWolkoff, да, согласна, создам пример, максимально приближенный к реальности.

Но поскольку существует еще другая проблема  :) , требующая решения - выводить в сводной таблице, созданной через Power Pivot, измерения без фактов (что в обычных сводных таблицах решается нажатием галочки "Отображать пустые элементы"), то, чувствую, все равно придется копать в сторону PQ (то, что предлагает PooHkrd) или изменениям на стороне SQL запроса.
Изменено: OksanaB - 12.04.2018 15:03:20
 
 
 
#24
12.04.2018 14:56:50
StepanWolkoff, в модель данные грузятся на прямую из SQL
 
 
 
#25
12.04.2018 15:01:50
Цитата
OksanaB написал: Что я делаю не так?
Не показываете ссылки на параллельное размещение вопроса.
Цитата
Публикуя один и тот же вопрос в разных форумах и на дружественных сайтах вы заставляете сразу нескольких людей параллельно думать над вашей задачей и обесцениваете усилия тех, кто даст ответ вторым-третьим и т.д.
 
 
 
#26
12.04.2018 15:10:56
vikttur, упс, простите, не знала о таких правилах.

все ответы ценю, всем, кто пытается помочь большое спасибо )
Изменено: OksanaB - 12.04.2018 15:11:10
 
 
 
#27
12.04.2018 15:14:35
Но теперь знаете, но упорно ссылки не даете. Ну, как так?
 
 
 
#28
12.04.2018 15:17:29
OksanaB, предлагаю вам оформить корректный пример и создать новую тему с адекватным названием. Все же здесь ваш вопрос это оффтоп. Заодно в той теме и ссылки на кросс-пост оформите. А здесь дайте ссылку на новую тему, чтобы все заинтересованные могли продолжать уже в ней.
Изменено: PooHkrd - 12.04.2018 15:18:41
Вот горшок пустой, он предмет простой...
 
 
 
#29
12.04.2018 15:58:21
Цитата
OksanaB написал:
ссылки где предлагалось реальное решение?
С этим сложнее, проще пример сделать.
Цитата
OksanaB написал:
в модель данные грузятся на прямую из SQL
Аналогично можно грузить и данные для измерений. Просто просмотрите код запроса, какие таблицы и столбцы формируют те столбцы, которые вы используете как источник строк и столбцов в сводной. Вот их уникальные значения и нужно выдернуть с сервера.
Изменено: Андрей VG - 12.04.2018 16:00:08
 
 
 
#30
13.04.2018 13:41:59
Цитата
StepanWolkoff написал:
Без справочников для модели просто не существует контекста 2016-продукт1, поэтому даже в сводной не просто пусто, а абсолютное ничто для меры, если так можно выразиться
Это называется AUTOEXISTS. Если столбцы из одной таблицы, то в рассмотрение берутся только имеющиеся сочетания значений этих столбцов, а не CROSSJOIN этих значений. Соответственно, несуществующие сочетания действительно даже не рассматриваются. Если же столбцы из разных таблиц, то тогда формируется условно CROSSJOIN, на который уже действуют ухищрения типа "+0" или "выводить 0".
F1 творит чудеса
 
 
 
Страницы: 1 2 След.
Наверх