Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Нахождение средневзвешенной цены при помощи DAX формул. - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Нахождение средневзвешенной цены при помощи DAX формул. (Формулы/Formulas)
Нахождение средневзвешенной цены при помощи DAX формул.
zzbear Дата: Вторник, 20.06.2017, 13:10 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Добрый день.

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

Вычисление решается просто при помощи обычных формул Excel СУММПРОИЗВ() или СУММЕСЛИМН()

Пример
Код
=СУММЕСЛИМН(рубли;торговая_марка;торговая_марка1;месяц;месяц1)/СУММЕСЛИМН(бутылки;торговая_марка;торговая_марка1;месяц;месяц1)


Но с учетом того что в массиве более 300 тыс строк то процесс вычисления может занять более суток.

Попробовал считать через POWERPIVOT при помощи DAX формулы CALCULATE() (пишут что это аналог СУММЕСЛИМН()) или при помощи SUMX() (аналога СУММПРОИЗВ() ,но никак не получается разобраться в синтаксисе.

На примере формулы [vba]
Код
CALCULATE(SUM([рубли]);[торговая марка]=[торговая марка1];[месяц]=[месяц1])/CALCULATE(SUM([бутылки]);[торговая марка]=[торговая марка1];[месяц]=[месяц1])
[/vba] выдает ошибку.

Какой корректный синтаксис отсылки на значение в конкретной ячейке надстройки POWERPIVOD, или такой возможности нет?


Сообщение отредактировал zzbear - Вторник, 20.06.2017, 13:36
 
Ответить
СообщениеДобрый день.

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

Вычисление решается просто при помощи обычных формул Excel СУММПРОИЗВ() или СУММЕСЛИМН()

Пример
Код
=СУММЕСЛИМН(рубли;торговая_марка;торговая_марка1;месяц;месяц1)/СУММЕСЛИМН(бутылки;торговая_марка;торговая_марка1;месяц;месяц1)


Но с учетом того что в массиве более 300 тыс строк то процесс вычисления может занять более суток.

Попробовал считать через POWERPIVOT при помощи DAX формулы CALCULATE() (пишут что это аналог СУММЕСЛИМН()) или при помощи SUMX() (аналога СУММПРОИЗВ() ,но никак не получается разобраться в синтаксисе.

На примере формулы [vba]
Код
CALCULATE(SUM([рубли]);[торговая марка]=[торговая марка1];[месяц]=[месяц1])/CALCULATE(SUM([бутылки]);[торговая марка]=[торговая марка1];[месяц]=[месяц1])
[/vba] выдает ошибку.

Какой корректный синтаксис отсылки на значение в конкретной ячейке надстройки POWERPIVOD, или такой возможности нет?

Автор - zzbear
Дата добавления - 20.06.2017 в 13:10
AndreTM Дата: Вторник, 20.06.2017, 16:00 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1545
Репутация: 444 ±
Замечаний: 0% ±

2003 & 2010
Нужен ваш пример в файле, с небольшим количеством тестовых данных.
А то вот так прямо непонятно, что у вас там именно задано именованными выражениями (какие именно списки, значения и прочее)


Donate: Qiwi: 9517375010
 
Ответить
СообщениеНужен ваш пример в файле, с небольшим количеством тестовых данных.
А то вот так прямо непонятно, что у вас там именно задано именованными выражениями (какие именно списки, значения и прочее)

Автор - AndreTM
Дата добавления - 20.06.2017 в 16:00
zzbear Дата: Вторник, 20.06.2017, 16:35 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
AndreTM,

Файл в приложении.

На первой вкладке как должно быть. (столбцы с формулами выделены желтым цветом)

На второй нужно повторить тоже самое в POWERPIVOT при помощи формул DAX
К сообщению приложен файл: 3626220.xlsx(18Kb)


Сообщение отредактировал zzbear - Вторник, 20.06.2017, 16:36
 
Ответить
СообщениеAndreTM,

Файл в приложении.

На первой вкладке как должно быть. (столбцы с формулами выделены желтым цветом)

На второй нужно повторить тоже самое в POWERPIVOT при помощи формул DAX

Автор - zzbear
Дата добавления - 20.06.2017 в 16:35
anvg Дата: Вторник, 20.06.2017, 17:42 | Сообщение № 4
Группа: Друзья
Ранг: Обитатель
Сообщений: 313
Репутация: 162 ±
Замечаний: 0% ±

2010, 2016
Доброе время суток.
Что-то так и не понял, что у вас искомое, что его вес. Принял искомое - цена, вес бут
Успехов.
К сообщению приложен файл: _3626220.zip(72Kb)
 
Ответить
СообщениеДоброе время суток.
Что-то так и не понял, что у вас искомое, что его вес. Принял искомое - цена, вес бут
Успехов.

Автор - anvg
Дата добавления - 20.06.2017 в 17:42
AndreTM Дата: Вторник, 20.06.2017, 22:22 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1545
Репутация: 444 ±
Замечаний: 0% ±

2003 & 2010
Я бы (думаю, и второй Андрей со мной согласится) вообще делал такой свод, используя возможности Power Query, а не PowerPivot.
Потому что если нужен именно только расчёт значений, с созданием таблицы (которую вы уже можете потом крутить на предмет группировки по нужным показателям и прочее) - то зачем использовать DAX, если всё можно сделать сразу в M?


Donate: Qiwi: 9517375010
 
Ответить
СообщениеЯ бы (думаю, и второй Андрей со мной согласится) вообще делал такой свод, используя возможности Power Query, а не PowerPivot.
Потому что если нужен именно только расчёт значений, с созданием таблицы (которую вы уже можете потом крутить на предмет группировки по нужным показателям и прочее) - то зачем использовать DAX, если всё можно сделать сразу в M?

Автор - AndreTM
Дата добавления - 20.06.2017 в 22:22
zzbear Дата: Среда, 21.06.2017, 10:16 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Добрый день.

К сожалению не удалось открыть приложенный вами файл. Пишет что версия EXCEL более ранняя.

Меня интересует на сколько идентичны функции EXCEL СУММЕСЛИМН() и СУММПРОИЗВ() с соответствующими функциями CFLCULATE() и SUMX()
Особенно интересует вопрос по поводу возможности отсылки в функции DAX на конкретную ячейку в качестве условия отбора c последующим протягиванием формулы, как это возможно в EXCEL, или все-таки такой возможности нет?
 
Ответить
СообщениеДобрый день.

К сожалению не удалось открыть приложенный вами файл. Пишет что версия EXCEL более ранняя.

Меня интересует на сколько идентичны функции EXCEL СУММЕСЛИМН() и СУММПРОИЗВ() с соответствующими функциями CFLCULATE() и SUMX()
Особенно интересует вопрос по поводу возможности отсылки в функции DAX на конкретную ячейку в качестве условия отбора c последующим протягиванием формулы, как это возможно в EXCEL, или все-таки такой возможности нет?

Автор - zzbear
Дата добавления - 21.06.2017 в 10:16
anvg Дата: Среда, 21.06.2017, 11:08 | Сообщение № 7
Группа: Друзья
Ранг: Обитатель
Сообщений: 313
Репутация: 162 ±
Замечаний: 0% ±

2010, 2016
Доброе время суток.
Странно, что у вас не открылась модель, обычно в новых версиях предлагается её преобразовать, после чего без проблем всё просматривается и меняется. Код функции меры в предложенном файле
[vba]
Код
=DIVIDE(SUMX('Products';'Products'[руб]*'Products'[бут]);SUM('Products'[бут]);BLANK())
[/vba]
Обратите внимание, что таблица переименована в Products.
Фитльтрацию (аналог ЕСЛИ) выполняют поля таблицы, размещённые в строках/столбцах сводной таблицы.
Успехов.
 
Ответить
СообщениеДоброе время суток.
Странно, что у вас не открылась модель, обычно в новых версиях предлагается её преобразовать, после чего без проблем всё просматривается и меняется. Код функции меры в предложенном файле
[vba]
Код
=DIVIDE(SUMX('Products';'Products'[руб]*'Products'[бут]);SUM('Products'[бут]);BLANK())
[/vba]
Обратите внимание, что таблица переименована в Products.
Фитльтрацию (аналог ЕСЛИ) выполняют поля таблицы, размещённые в строках/столбцах сводной таблицы.
Успехов.

Автор - anvg
Дата добавления - 21.06.2017 в 11:08
zzbear Дата: Среда, 21.06.2017, 11:28 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
anvg, Спасибо большое
 
Ответить
Сообщениеanvg, Спасибо большое

Автор - zzbear
Дата добавления - 21.06.2017 в 11:28
zzbear Дата: Среда, 21.06.2017, 13:29 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
anvg, Добрый день еще раз проанализировал вашу формулу, попробовал использовать.

Результат не сходится с необходимым.

По условию необходимо найти средневзвешенную цену бренда (искомое) с учетом объемов реализации (вес), а также учитывая следующие факторы (фильтры): принадлежность к определенному городу, объем продукта (вид тары), месяц реализации
К сообщению приложен файл: 2372916.rar(72Kb)


Сообщение отредактировал zzbear - Среда, 21.06.2017, 13:29
 
Ответить
Сообщениеanvg, Добрый день еще раз проанализировал вашу формулу, попробовал использовать.

Результат не сходится с необходимым.

По условию необходимо найти средневзвешенную цену бренда (искомое) с учетом объемов реализации (вес), а также учитывая следующие факторы (фильтры): принадлежность к определенному городу, объем продукта (вид тары), месяц реализации

Автор - zzbear
Дата добавления - 21.06.2017 в 13:29
zzbear Дата: Среда, 21.06.2017, 14:27 | Сообщение № 10
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
AndreTM, С запросами не пробовал работать. Если это действительно ускоряет работу то надо попробовать.
 
Ответить
СообщениеAndreTM, С запросами не пробовал работать. Если это действительно ускоряет работу то надо попробовать.

Автор - zzbear
Дата добавления - 21.06.2017 в 14:27
anvg Дата: Среда, 21.06.2017, 14:42 | Сообщение № 11
Группа: Друзья
Ранг: Обитатель
Сообщений: 313
Репутация: 162 ±
Замечаний: 0% ±

2010, 2016
необходимо найти средневзвешенную цену бренда (искомое) с учетом объемов реализации (вес)

Дошло, что вы под этим подразумеваете :) У вас в столбце руб уже стоимость бут. Увы, я не продажник.
[vba]
Код
=DIVIDE(SUM('Products'[руб]);SUM('Products'[бут]);BLANK())
[/vba]
Успехов.
 
Ответить
Сообщение
необходимо найти средневзвешенную цену бренда (искомое) с учетом объемов реализации (вес)

Дошло, что вы под этим подразумеваете :) У вас в столбце руб уже стоимость бут. Увы, я не продажник.
[vba]
Код
=DIVIDE(SUM('Products'[руб]);SUM('Products'[бут]);BLANK())
[/vba]
Успехов.

Автор - anvg
Дата добавления - 21.06.2017 в 14:42
zzbear Дата: Среда, 21.06.2017, 15:11 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
anvg, Нет, к сожалению не то.


Сообщение отредактировал zzbear - Среда, 21.06.2017, 15:11
 
Ответить
Сообщениеanvg, Нет, к сожалению не то.

Автор - zzbear
Дата добавления - 21.06.2017 в 15:11
zzbear Дата: Среда, 21.06.2017, 15:30 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
AndreTM, Попробовал через запрос осуществить решение проблемы, достиг нужных значений но не в необходимой форме :)

Посредством группировки и простейшей операции деления прихожу к правильному результату, но выводимая таблица представляет из себя только данные которые были выбраны для группировки и соответственно происходит не добавление дополнительных вычисляемых столбцов к общему массиву, а образуется новый, но уже сокращенный массив....
 
Ответить
СообщениеAndreTM, Попробовал через запрос осуществить решение проблемы, достиг нужных значений но не в необходимой форме :)

Посредством группировки и простейшей операции деления прихожу к правильному результату, но выводимая таблица представляет из себя только данные которые были выбраны для группировки и соответственно происходит не добавление дополнительных вычисляемых столбцов к общему массиву, а образуется новый, но уже сокращенный массив....

Автор - zzbear
Дата добавления - 21.06.2017 в 15:30
AndreTM Дата: Среда, 21.06.2017, 16:15 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1545
Репутация: 444 ±
Замечаний: 0% ±

2003 & 2010
Через запросы ты сможешь получать правильные данные, только если начнешь формировать запрос динамически, уже с учетом выставленного тобой текущего фильтра в отчете.
Либо все же добивай DAX-формулами, только не забудь конечную формулу обернуть в CALCULATE() с использованием FILTER() для нужных тебе группировок - тогда в отчете формула динамически подсчитает правильные данные с учетом текущих фильтров.


Donate: Qiwi: 9517375010
 
Ответить
СообщениеЧерез запросы ты сможешь получать правильные данные, только если начнешь формировать запрос динамически, уже с учетом выставленного тобой текущего фильтра в отчете.
Либо все же добивай DAX-формулами, только не забудь конечную формулу обернуть в CALCULATE() с использованием FILTER() для нужных тебе группировок - тогда в отчете формула динамически подсчитает правильные данные с учетом текущих фильтров.

Автор - AndreTM
Дата добавления - 21.06.2017 в 16:15
anvg Дата: Среда, 21.06.2017, 16:59 | Сообщение № 15
Группа: Друзья
Ранг: Обитатель
Сообщений: 313
Репутация: 162 ±
Замечаний: 0% ±

2010, 2016
Нет, к сожалению не то.
Вы можете предоставить более детальное описание? Вставил ваши расчётные данные к результату вычисления DAX - разница ноль.
К сообщению приложен файл: 4566234.zip(72Kb)


Сообщение отредактировал anvg - Среда, 21.06.2017, 17:00
 
Ответить
Сообщение
Нет, к сожалению не то.
Вы можете предоставить более детальное описание? Вставил ваши расчётные данные к результату вычисления DAX - разница ноль.

Автор - anvg
Дата добавления - 21.06.2017 в 16:59
zzbear Дата: Среда, 21.06.2017, 22:28 | Сообщение № 16
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Спасибо всем.

Просто совсем недавно начал использовать оба инструмента.

Пока сложно отойти от логики вычисления обычными формулами EXCEL

Немного трудно понять как происходит расчет не видя конечный результат моментально.

anvg, Действительно ваша формула работает при корректном выставлении фильтров в финальном отчете. Спасибо.
 
Ответить
СообщениеСпасибо всем.

Просто совсем недавно начал использовать оба инструмента.

Пока сложно отойти от логики вычисления обычными формулами EXCEL

Немного трудно понять как происходит расчет не видя конечный результат моментально.

anvg, Действительно ваша формула работает при корректном выставлении фильтров в финальном отчете. Спасибо.

Автор - zzbear
Дата добавления - 21.06.2017 в 22:28
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Нахождение средневзвешенной цены при помощи DAX формул. (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!