Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power BI мера для нахождения пропавших контрагентов
 
#1
25.06.2019 16:48:45
Добрый день!

Есть таблица с продажами следующего вида:
Контрагент количестводата
Контрагент 11015 Май 2019 г.
Контрагент 32017 Май 2019 г.
Контрагент 43018 Май 2019 г.
Контрагент 54019 Май 2019 г.
Контрагент 65020 Май 2019 г.
Контрагент 76021 Май 2019 г.
Контрагент 87022 Май 2019 г.
Контрагент 1511 Июнь 2019 г.
Контрагент 21512 Июнь 2019 г.
Контрагент 32513 Июнь 2019 г.
Контрагент 43514 Июнь 2019 г.
Контрагент 54515 Июнь 2019 г.
Контрагент 65516 Июнь 2019 г.
Необходимо вывести пропавших контрагентов, то есть: Контрагент 7 брал 21 мая 2019г. Контрагент 8 брал 22 мая 2019г., но они не брали в июне, соответственно их должна отобразить мера, желательно со всеми предыдущими им продажами (до мая месяца, они тоже есть в таблице, а представлена только часть данных.)
 
 
 
#2
25.06.2019 17:41:38
говорят файл пример, решает множество проблем, но это не точно.тыц
 
 
 
#3
25.06.2019 17:46:55
Цитата
NIkSh написал:
их должна отобразить мера, желательно со всеми предыдущими им продажами
Как вы себе это представляете?
Цитата
Wild.Godlike написал:
говорят файл пример, решает множество проблем
они все врут! :D
Вот горшок пустой, он предмет простой...
 
 
 
#4
25.06.2019 17:49:33
Прикладываю в Экселе, так как в PB как раз так и не получается)
 
 
 
#5
25.06.2019 17:54:43
Т.е. по сути это обычная сводная, но вывести надо только тех, у кого нет продаж в последнем месяце?
Вот горшок пустой, он предмет простой...
 
 
 
#6
25.06.2019 17:57:59
По сути да, в вычисляемом столбце у меня даже получился нужный результат, но мне необходимо применять к этим данным различные срезы, что не позволяет сделать столбец
 
 
 
#7
25.06.2019 18:59:53
если делать с календарем, то вот такое надумалось:
Код
Потеряшки:=
var maxyear = CALCULATE( YEAR(MAX('Таблица1'[дата])); ALL('Таблица1'[Контрагент]); ALL('Календарь') ) 
var maxmonth = CALCULATE( MONTH(MAX('Таблица1'[дата])); ALL('Таблица1'[Контрагент]); ALL('Календарь') ) 
var checksales = CALCULATE( SUM('Таблица1'[количество]); FILTER( CALCULATETABLE( 'Таблица1'; ALL('Календарь') ); 'Таблица1'[дата]>=DATE(maxyear;maxmonth;1) ) )
return
IF( ISBLANK(checksales); SUM('Таблица1'[количество]); BLANK() )

Может и нагородил лишнего, но вроде работает.
Вот только как вам вывести чтобы показывались пустые ячейки в июне, но при этом не показывались остальные КА хоть убейте не знаю. Все предыдущие продажи за все месяцы до последнего мера вам покажет.
Изменено: PooHkrd - 25.06.2019 19:03:24 (навел красоты ;))
Вот горшок пустой, он предмет простой...
 
 
 
#8
25.06.2019 21:24:15
Доброе время суток.
Цитата
PooHkrd написал:
как вам вывести чтобы показывались пустые ячейки в июне, но при этом не показывались остальные КА
Ну, в Excel - это как раз не проблема. Вот Power BI, да, боюсь, никак.
Изменено: Андрей VG - 25.06.2019 21:24:31
 
 
 
#9
26.06.2019 10:13:44
Андрей VG, ну, так я и сам могу :D , но я имел ввиду именно июнь, как было показано в хотелке ТС.
Вот горшок пустой, он предмет простой...
 
 
 
#10
26.06.2019 10:42:28
Цитата
PooHkrd написал:
так я и сам могу  , но я имел ввиду именно июнь, как было показано в хотелке ТС.
Так для этого достаточно не создавать избыточный календарь - не будет лишних месяцев :)
Изменено: Андрей VG - 26.06.2019 10:42:42
 
 
 
#11
26.06.2019 10:48:02
Андрей VG, ну, вот такой он автокалендарь в РР Экселя, как там в PBI я не знаю, может избыточного и не генерит. Через PQ лениво было заморачиваться. Но вообще да, это как раз вариант решения. Хотя, по фигу, ТС хотел решение именно для PBI.
Вот горшок пустой, он предмет простой...
 
 
 
#12
26.06.2019 12:10:48
В Экселе все работает, но в BI, почему то отображает только в разрезе годов и кварталов (для месяцев и дней просто ничего не выводит)
 
 
 
#13
27.06.2019 12:07:04
Так, что ли, надо?
Тогда так, наверное:
Код
Lost = 
VAR _TotalSales = CALCULATE([Sales];ALL('Таблица2'[Контрагент ]))
VAR _StartDate = MIN('Календарь'[Date])
VAR _CurrentCustomers = VALUES('Таблица2'[Контрагент ])
VAR _CustWithSalesInPrevPeriod =CALCULATETABLE(VALUES('Таблица2'[Контрагент ]);'Календарь'[Date]<_StartDate)
VAR _LostCustomers = EXCEPT(_CustWithSalesInPrevPeriod;_CurrentCustomers)
VAR _LCSales = CALCULATE([Sales];KEEPFILTERS(TREATAS(_LostCustomers;'Таблица2'[Контрагент ]));'Календарь'[Date]<_StartDate)
RETURN
IF(NOT(ISBLANK(_TotalSales));_LCSales)

Только обязательно измените связь 1:1 двустороннюю между календарем и таблицей на 1:много одностороннюю
F1 творит чудеса
 
 
 
#14
27.06.2019 14:03:09
А столбец [Sales] это SUM('Таблица2'[количество])?
и поясните пожалуйста эту строку, как она работает и что делает
CALCULATE([Sales];KEEPFILTERS(TREATAS(_LostCustomers;'Таблица2'[Контрагент ]));'Календарь'[Date]<_StartDate)
 
 
 
#15
27.06.2019 15:20:02
Цитата
NIkSh написал:
А столбец [Sales] это SUM('Таблица2'[количество])?
да, только это не столбец, а мера.
чтобы было понятнее нагляднее без ошибок, всегда пишите название таблицы перед ссылкой на столбец и никогда - перед ссылкой на меру. И будет щастье.
Цитата
NIkSh написал:
поясните пожалуйста эту строку
Код
CALCULATE([Sales];

считает меру Sales, которая рассчитывается в следующих условиях:
Первое условие:
Код
KEEPFILTERS(TREATAS(_LostCustomers;'Таблица2'[Контрагент ]));

TREATAS оставляет в столбце 'Таблица2'[Контрагент ] только те значения, которые есть в _LostCustomers.
KEEPFILTERS при этом дополняет этот отбор тем значением контрагента, которое пришло из фильтра по контрагентам, который образовался из значений строк матрицы.
Соответственно, когда встречаем "живого" контрагента, у нас противоречие и мы получаем пустой набор контрагентов. Сумма по пустому набору = пусто.
Когда у нас в строке потерянный контрагент, мы получаем его одного. Соответственно сумма по одному и есть тот самый [Sales]
В итоговой строке фильтра по контрагентам нет, соответственно остаются только те значения контрагентов, которые потерялись (то есть находятся в _LostCustomers). Соответственно [Sales] будет суммироваться только по потерянным.
Второе условие:
Код
'Календарь'[Date]<_StartDate)

все даты календаря меньше даты, определенной как стартовая дата текущего периода (месяц, день, квартал, год...)

Оба условия действуют одновременно
F1 творит чудеса
 
 
 
Страницы: 1
Наверх