Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Подсчет количества через PowerPivot, Как посчитать количества в DAX
 
#1
17.05.2018 15:32:01
Добрый день!

Помогите, пожалуйста, с подсчетом количества через PowerPivot.
Есть табличка, в котором есть основной город и дополнительный.

Нужно в одной сводной (один уникальный город-одна строка) видеть сумму количества по ГородОсновной и ГородДополнительный (см. пример)
 
 
 
#2
17.05.2018 16:37:28
Я правильно понимаю, что вам все ж таки нужно посчитать сумму по полю Количество, но в разрезе столбца Город2 и вывести результат по измерениям выставленным из столбца Город1?
Вот горшок пустой, он предмет простой...
 
 
 
#3
17.05.2018 16:57:41
В общем нужно сделать доп. таблицу, из которой будете выводить измерения, и добавить неактивную связь между этой таблицей и столбцом Город2. А мера будет такая:
Код
КолВоГород2:=
CALCULATE(
     SUMX(RELATEDTABLE('Таблица1');[Количество]);
     USERELATIONSHIP('Таблица1'[Город2];'Справочник_Города'[Город]))
Изменено: PooHkrd - 17.05.2018 16:58:38
Вот горшок пустой, он предмет простой...
 
 
 
#4
17.05.2018 16:59:39
Доброе время суток.
Цитата
PooHkrd написал:
Я правильно понимаю, что вам все ж таки нужно посчитать сумму по полю Количество, но в разрезе столбца Город2 и вывести результат по измерениям выставленным из столбца Город1?
Коллега, а фиг его знает. По объединению ячеек с текстом "В этих столбцах должны быть..." вроде как нет (с другой стороны присутствует ссылка на столбец L, который входит в объединение, что и сбивает). Так что вариант: отдельно количество по Город1, отдельно по Город2. Версия на 2010.
 
 
 
#5
17.05.2018 17:08:31
Андрей VG, странная ерунда, я изначально написал меру такую же как у вас, но она считала некорректно, потому пришлось извращаться с RELATEDTABLE.
Вот горшок пустой, он предмет простой...
 
 
 
#6
17.05.2018 17:18:07
Да, нужно чтобы столбец K и L имели одинаковые значения. Спасибо за предложенные варианты, но с доп. таблицей сделать вообще не проблема ))

"фишка" как раз в том, чтобы сделать это в рамках одной таблицы (листа) PowerPivot. Через выражение DAX. Может создать виртуальную таблицу и там посчитать, потом добавить это значение или как то еще. В этом как раз проблема
 
 
 
#7
17.05.2018 17:29:02
Цитата
Raritet написал:
"фишка" как раз в том
Offtop
Гланды через одно место автогеном.  :D
 
 
 
#8
17.05.2018 17:36:00
А месье знает толк в извращениях!
Пробуйте:
Код
КоличествоГород2:=
   AVERAGEX(
      ADDCOLUMNS(
         'Таблица1';
         "СуммаПоГороду2";
         CALCULATE(SUM([Количество]);ALL('Таблица1');'Таблица1'[Город2]=EARLIER([Город1]))
      );
      [СуммаПоГороду2]
   )

Нужно понимать что корректно данная формула будет работать только если списки уникальные значения городов в столбцах Город1 и Город2 будут совпадать. Иначе результаты могут вас сильно удивить. :D
Изменено: PooHkrd - 17.05.2018 17:39:36
Вот горшок пустой, он предмет простой...
 
 
 
#9
17.05.2018 17:38:28
Почему? Зачем создавать доп. лист и там считать, если умная машина может сделать это "в один заход" формулой!?

К тому же там количество листов уже за десять перевалило, так что если есть возможность не плодить лишние листы, я бы сделал без них
 
 
 
#10
17.05.2018 17:41:24
Цитата
Raritet написал:
Почему? Зачем создавать доп. лист и там считать, если умная машина может сделать это "в один заход" формулой!?
Потому что чем проще формула, тем проще потом работать с моделью.
А так накрутите всякой фигни. А потом тут появится тема типа "Что-то у меня моделька тормозит."
По быстродействию работа со связями, даже неактивными обсчитывается на порядок быстрее чем любые костыли, вы себе или мы вам тут насочиняем.
Изменено: PooHkrd - 17.05.2018 17:43:14
Вот горшок пустой, он предмет простой...
 
 
 
#11
17.05.2018 17:49:03
Что-то я вообще не сталкивался с проблемой быстродействия в PowerPivot, хотя есть файлы с десятком листов, сотнями тысяч строк и 5-6 мерами.....мне кажется он работает мгновенно!

Так что за производительность я не переживаю, только за красоту ))


p.s. + порой в связях между листами разобраться сложнее, чем разобрать формулу DAX
 
 
 
#12
17.05.2018 18:03:14
Вот сегодня ковырялся с моделью где сопоставлял 2 справочника на 60 млн строк и на 20 тыс строк. Так Excel сожрал 32 гига памяти, сказал что ему мало и плюнул на меня. Пришлось извращаться с оптимизацией.
З.Ы. Формула-то устраивает?
Изменено: PooHkrd - 17.05.2018 18:04:36
Вот горшок пустой, он предмет простой...
 
 
 
#13
17.05.2018 18:05:03
Цитата
PooHkrd написал:
Пробуйте:
Вариант.
Цитата
Raritet написал:
Так что за производительность я не переживаю, только за красоту ))
Я так понимаю, вы очередной практик? Пока гром не грянет...  
Пока вы в мире простых данных и мер живёте.
Цитата
Raritet написал:
К тому же там количество листов уже за десять перевалило, так что если есть возможность не плодить лишние листы
А не проще ли, чтобы не плодить посидеть, да продумать структуру данных, может в базу заводить, что уже не изменяется в PQ сливать для Power Pivot? А то вы рассуждаете как этот товарищ. Зачем одну и туже трансформацию 100500 раз делать? Лучше сразу приучать себя делать правильно, как в книжках пишут люди, уже наступавшие на грабли.
 
 
 
#14
22.05.2018 14:55:56
Прошу прощения, не мог смотреть тему - был в отъезде

Цитата
PooHkrd написал:
З.Ы. Формула-то устраивает?
Не очень. В целом работает, но она берет среднее. Поменял на SumX, но там как-то вообще странно стало считаться

Цитата
Андрей VG написал:
Я так понимаю, вы очередной практик? Пока гром не грянет...  Пока вы в мире простых данных и мер живёте.
Всегда надо сохранять баланс между производительностью и читабельностью. В примере там уйма связей между таблицами, естественно при изменении данных пока каждая обновится - "сто лет" пройдет. А вот если бы там все было бы посчитано формулами - есть шанс что этот бы самолет и "взлетел"
Цитата
Андрей VG написал:
А не проще ли, чтобы не плодить посидеть, да продумать структуру данных, может в базу заводить, что уже не изменяется
Все продумано. Сливать данные в PQ нельзя, т.к. изменения происходят в данных с глубиной минимум год, что равно примерно половине данных. Учитывая что все это сейчас считается почти мгновенно не вижу смысла "утяжелять" модель дополнительными телодвижениями в PQ

Андрей VG - Так же не могу посмотреть формулу в приложенном файле - ни у кого на работе нет Excel 2010 )) Там через Relationship сделано? Если да, то можно и не смотреть, такой вариант я могу сделать сам.
 
 
 
#15
22.05.2018 15:52:38
Цитата
Raritet написал:
Не очень. В целом работает, но она берет среднее.
А вы точно разбирались в том как она работает? AVARAGEX здесь нужен только для того чтобы корректно выводить в ячейки сводной содержимое расчетного столбца, который формируется формулой ADDCOLUMNS, а сами суммы считаются внутри формулы CALCULATE, которая рвет все контексты и устанавливает связь мужду столбцами Город1 и Город2. Вместо AVARAGEX с тем же успехом можно взять любой другой агрегатор, который не суммирует значения, те же MAXX/MINX
Если с этим объяснением не понятно как оно работает, то попробуйте создать в модели расчетный столбец с формулой
Код
=CALCULATE(SUM([Количество]);ALL('Таблица1');'Таблица1'[Город2]=EARLIER([Город1]))

Так будет нагляднее.
Вот формула Андрея, которая работает без неактивных связей.
Код
ыСумма кол-во город2:=IF(HASONEVALUE('Исходные'[Город1]);
CALCULATE(SUM('Исходные'[Количество]); FILTER(ALL('Исходные'); 'Исходные'[Город2] = VALUES('Исходные'[Город1])));
[ыСумма кол-во гор1])
Изменено: PooHkrd - 22.05.2018 15:58:39
Вот горшок пустой, он предмет простой...
 
 
 
#16
22.05.2018 16:08:57
Raritet, нежелание использовать PQ, на мой взгляд, ошибочное решение. Прикладываю файл, где достаточно 2х "кнопочных" преобразований, чтобы не городить потом в PowerPivot различные извращения, а решить все простейшими мерами.
 
 
 
#17
22.05.2018 17:07:59
Можно и на одной таблице, но именно что вот так:
Цитата
Андрей VG написал:
Гланды через одно место автогеном.
Код
=
SUMX (
    VALUES ( 'Таблица1'[Город1] );
    CALCULATE (
        [КолВоГород1];
        ALL ( 'Таблица1'[Город1] );
        FILTER (
            ALL ( 'Таблица1'[Город2] );
            'Таблица1'[Город2] = CALCULATETABLE ( VALUES ( 'Таблица1'[Город1] ) )
        )
    )
)
F1 творит чудеса
 
 
 
#18
22.05.2018 17:51:17
Цитата
Максим Зеленский написал:
Можно и на одной таблице, но именно что вот так:
Лучший и самый правильный вариант! т.к. во всех остальных выражениях сумма не раскладывалась на месяца. Т.е. если смотреть сводно "Город"-"Кол-во", то все сходится, но как только начинаешь добавлять доп. столбцы, то происходит"сумма значений всего" для каждой строки по городу

Думаю что и остальные формулы можно модифицировать (чем и занимался), но у Максима все учтено "в коробочном варианте" ))

Всем спасибо, очень помогли! Все варианты достойные
 
 
 
#19
22.05.2018 18:43:33
для оффтопа: насколько элегантнее формула в Power BI:
Код
КолВо по Город2 =
CALCULATE (
    [КолВо];
    ALL ( 'Таблица1'[Город1] );
    'Таблица1'[Город2] IN VALUES ( 'Таблица1'[Город1] )
)
Изменено: Максим Зеленский - 22.05.2018 18:45:23 (и даже еще элегантнее)
F1 творит чудеса
 
 
 
#20
23.05.2018 09:21:19
Блин, здесь итальянцы пишут, что IN должен быть в Экселе с февральских обновлений 2017 года. А его нет. Обыдна, да!
Изменено: PooHkrd - 23.05.2018 09:22:18
Вот горшок пустой, он предмет простой...
 
 
 
#21
23.05.2018 10:47:10
Цитата
PooHkrd написал:
А его нет. Обыдна, да!
А у вас Office 365? :)
F1 творит чудеса
 
 
 
#22
23.05.2018 10:50:22
А, это только для него. Нет - Проф +
Вот горшок пустой, он предмет простой...
 
 
 
#23
23.05.2018 13:22:17
PooHkrd, Проф+ бывает и 365. Просто все обновления и новый функционал появляются именно в 365 (то есть версиях по подписке). Если у вас VL или standalone (так называемая msi-инсталляция), то что до нее доедет и когда - х.з.
F1 творит чудеса
 
 
 
Страницы: 1
Наверх