Страницы: 1
RSS
Итоговый столбец с формулой в сводной таблице
 
Добрый день.
В очередной раз нужна помощь по сводным таблицам...
Есть сводная, сокращенный образец которой я прикладываю. В ней в качестве заголовков строк идут даты и курсы валют на эти даты, а в качестве заголовков столбцов идут подстатьи расходов и валюты (поскольку платежи могут делаться в разных валютах).
Можно ли сделать так, чтобы после столбцов всех четырех валют по каждой подстатье шел итоговый столбец, который по формуле подсчитывал общую сумму по дате в какой-то конкретной валюте, например в долларах?.. То есть, говоря конкретно, чтобы после столбца G, например, шел столбец, который бы считал что-то похожее на такую формулу:
Код
= D6 + (E6/C6*B6) + (F6*B6) + (G6*B6)

Как добавлять вычисляемые поля в область значений - я смотрел, но в данном случае можно ли добавить такое вычисляемое поле в качестве столбцов итогов по подстатьям?..

Буду признателен за подсказки.
 
Доброе время суток.
Такое возможно только с использованием модели Power Pivot. Нужно создать ассиметричную меру, которая в зависимости от контекста считает сумму для рядовых вычислений и вашу формулу для промежуточных итогов.
 
Цитата
Андрей VG написал:
создать ассиметричную меру
Ого... Для меня это пока совсем сложно. А не подскажете, где можно про это почитать более-менее доступно? Именно про эти "меры"... Про Power Pivot я обзорно читал несколько статей, но таких подробностей там не встречал...
 
Цитата
Karn написал:
А не подскажете, где можно про это почитать более-менее доступно?
Реализации можно и на этом форуме (на самом деле решений было больше, чем выдаёт поиск - просто не всегда их так обзывали :) ). Можно почитать следующую статью How to Realize Dynamic Asymmetric Reports in Power BI / Excel
 
Андрей VG,спасибо большое, будем учиться...

Цитата
Нужно создать ассиметричную меру, которая в зависимости от контекста считает сумму для рядовых вычислений и вашу формулу для промежуточных итогов.
А вы не могли бы хотя бы немного раскрыть этот момент поподробнее?.. Я начал читать в разных местах про меры, но пока очень путаюсь даже в простых примерах, не говоря уже про "в зависимости от контекста считает сумму для рядовых вычислений"... Можете написать хотя бы примерную формулу для такой меры?.. Чтобы мне было, от чего отталкиваться... Спасибо заранее.
 
Цитата
Karn написал:
Можете написать хотя бы примерную формулу для такой меры?.
Да не проблема. Версия Power Pivot 2010. Только мне ваша формула для подитогов не нравиться
Цитата
Karn написал:
= D6 + (E6/C6*B6) + (F6*B6) + (G6*B6)
Как то странно это выглядит сумма долларов + произведение курса гривны на сумму гривны - вы не находите? :)
 
Цитата
Андрей VG написал: Как то странно это выглядит ... вы не находите?
упс... Таки наверное да, это я заступил... Там нужно делить, конечно, а не множить)) чтобы в итоге все суммы пришли к доллару.
сам файл посмотреть сейчас уже не могу с планшета, так что завтра. Но заранее большое вам спасибо за быстрые ответы и желание помочь)
 
Андрей VG, Еще раз большое спасибо за ваш пример. Вроде бы даже начал что-то понимать)...
А можно уточнить по поводу формулы этой асинхронной меры?
Код
AM:=IF(ISFILTERED('Дані'[Валюта]); SUM('Дані'[Сума]); [BgToD] + [DSum] + [EToD] + [GrToD])

Хотелось бы узнать, что именно проверяет условие ЕСЛИ? Что такое функция ISFILTERED ? насколько я могу понять, она проверяет на "отфильтрованность" столбец "Валюта"?.. И "если да", то в значение меры берется просто сумма по полю "Сума", иначе - суммируются результаты четырех вычислений. Просто не совсем понимаю этот ISFILTERED... Я бы по своей глупости просто написал что-то типа AM:=[BgToD] + [DSum] + [EToD] + [GrToD]  - и потом бы удивлялся, почему что-то работает не так... Понимаю, что там какая-то хитрость, но пока не пойму, какая)...
Изменено: Karn - 13.02.2019 14:27:20
 
Цитата
Karn написал:
Что такое функция ISFILTERED
- это функция.
Цитата
Karn написал:
она проверяет на "отфильтрованность" столбец "Валюта"?
Да. Вы можете сами посмотреть её результат, использовав её как меру. Сводная на базе модели Power Pivot может в результатах меры отображать не только числовые значения, но логические и текстовые значения. Главное, чтобы это был всегда один тип данных.
В общем, чаще экспериментируйте. Таких функций, на базе значений которых определяют положение вычислений несколько: HASONEFILTER, HASONEVALUE, ISCROSSFILTERED
Изменено: Андрей VG - 13.02.2019 15:20:27
 
Андрей VG, Добрый день снова. А можно еще раз вернуться к моему примеру?..
В выложенном вами файле я вроде бы более-менее поэкспериментировал, много чего интересного для себя понял, и он стал стимулом почитать вообще про формулы DAX и т.п., поэтому еще раз большое спасибо Вам!)
А можно уточнить один момент?
Можно как-то сделать так, чтобы в сводной таблице отображались ВСЕ валюты, а не только те, по которым есть данные?.. В обычных сводных таблицах, насколько я знаю, за это отвечает галочка в свойствах поля "Отображать пустые элементы", а вот в данном случае эта галочка почему-то недоступна... Попробовал в своем файле сделать все заново - результат тот же. Можно как-то сделать, чтобы отображались все валюты?..
Заранее спасибо за ответ)
 
Жмите галки в свойствах сводной таблицы как по ссылке. Но, только эти галки не помогут, Т.к. вы используете фильтры, то даже нажатие этих галок не создаст контекста, для формирования столбцов с валютами, по которым нет данных в отфильтрованных строках. Для этого придется делать справочник валют, создавать связь его с таблицей фактов и выводить в столбцы именно столбцы справочника. Тогда получится все как вы хотели.
Изменено: PooHkrd - 26.02.2019 17:59:33
Вот горшок пустой, он предмет простой...
 
PooHkrd, Хм... Что-то я запутался. Я добавил в свой файл таблицу валют, отметил галки как вы указали. Ок, теперь столбцы отображаются. Но теперь в итоге не считает сумму в пересчете на доллары, как было раньше. То есть я понимаю, что теперь что-то "поломалось" в тех мерах, которые были созданы в этом файле ранее... Как теперь правильно изменить меры, чтобы снова в итогах по каждой подстатье получился пересчет в долларах?..
Я пытался исправить формулы в мерах, заменяя в столбце "Валюта" название таблицы на название таблицы справочника, но так не получается... К сожалению, пока моих знаний по мерам и формулам DAX явно не хватает...
 
Цитата
Karn написал:
Но теперь в итоге не считает сумму в пересчете на доллары, как было раньше.
Менять нужно только итоговую меру:
Код
AM:=IF(ISFILTERED('Дані1'[Валюта]); SUM('Дані'[Сума]); [BgToD] + [DSum] + [EToD] + [GrToD])

Смысл в том, что теперь в сводной контекст фильтра по валюте задается строками из другой таблицы, значит функцией ISFILTERED нужно проверять наличие фильтров именно в ней.
Изменено: PooHkrd - 26.02.2019 09:29:02
Вот горшок пустой, он предмет простой...
 
PooHkrd, Спасибо огромное за разъяснение, теперь понял и работает)).
 
И снова возвращаясь к моему примеру...
Для полного счастья не хватает промежуточных итогов по месяцам по строкам. То есть у нас есть в заголовках строк поле "Дата", и нужно чтобы после всех дат одного месяца шла итоговая строка с суммами по месяцу. Казалось бы, чего проще. Но почему-то неактивна кнопка "Группировка по полю", которой я обычно раньше создавал по дате группировку по месяцам... Это потому, что сводная сделана на основе PowerPivot?.. И как можно сделать эту группировку, подскажите, пожалуйста?..
 
Вопрос не по теме
 
Karn, ничего не знаю. У меня все работает в вашем примере.
Вот горшок пустой, он предмет простой...
 
PooHkrd, Какая-то мистика... Вот я скачал последний из файлов примера, и у меня на ленте на вкладке "Анализ" кнопка "Группировка по полю" просто неактивна... А у вас, получается, она в этом же файле доступна? Или вы вызвали показанное на вашем рисунке окно группировки как-то иначе?..
 
Цитата
Karn написал:
как-то иначе?..
ПКМ по дате прямо в сводной и выбрать пункт Группировка.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
ПКМ по дате прямо в сводной и выбрать пункт Группировка.
Аналогично - пункт меню неактивен... Только что прочитал, что в Экселе 2016 добавлена функция автоматической группировки полей дат. может от этого зависит?.. У меня 2013. Хотя мне почему-то кажется, что вряд ли...
Изменено: Karn - 26.02.2019 17:56:34
 
Karn, никто вам не мешает в модели создать доп. столбец с месяцами и использовать его для группировки в любом Экселе. Посмотрите пример в моем первом посте. я там подкорректировал слегка.
З.Ы. Хотя для работы с датами и их группировками, я бы создал еще табличку-календарь, и выводил бы в сводную строки из неё. К тому же все гуру ПоверПивота это же самое настоятельно рекомендуют делать - а они не дураки.  ;)
Изменено: PooHkrd - 26.02.2019 17:34:50
Вот горшок пустой, он предмет простой...
 
PooHkrd,Ух ты) А можно про табличку-календарь чуть поподробнее? Или где можно почитать? Мне пару раз попадалось упоминание, но без деталей...

И спасибо за совет про создание столбца в источнике, скорее всего так и сделаю... А то совершенно непонятно, почему в моем случае недоступна эта группировка...
Изменено: Karn - 26.02.2019 17:49:53
 
Чтобы создать календарь в 2016 в РР есть кнопка. Как там в 2013 я не в курсе. Даже если в нем нет, то можно генерить календарь в запросе PQ и запихивать его в модель. поищите по форуму тут варианты таких запросов выкладывались.
З.Ы. нашел у себя файлик в загашнике, там запрос для генерации календаря.
З.З.Ы. Обновил файлик в первом посте - добавил туда календарь и связал с таблицей фактов. В нем автоматически создается еще и иерархия, на этом примере можно посмотреть зачем она нужна.
З.З.З.Ы. И завязывайте уже в этой теме задавать вопросы не связанные с её названием. А то вон модератор ругается.  ;) Может и прикрыть.
Изменено: PooHkrd - 26.02.2019 18:08:28
Вот горшок пустой, он предмет простой...
 
PooHkrd, огромное вам спасибо за помощь и разъяснения!
Страницы: 1
Наверх