Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сложный подсчет кол-ва уникальных Клиентов по их Структуре в общем объеме
 
#1
27.09.2020 16:16:48
1. Я выстроил рэнж от 1 до 100, предполагается, что это структура в процентах.
2. Оригинальная таблица, в которой идут расчеты (модель данных) имеет столбик с расчетами накопительной структуры Клиента от 0 до 100%.
3. Требуется посчитать количество уникальных Клиентов (Distinctcount), в каждом из случаев от 1 до 100 (п.1 выше).

Ожидается такое:
|TOP %|Client Qty|
|1          |0             |
|2          |5             |
|...         |...            |
|100      |6500       |

Т.е. 1 по TOP % это 1%, система должна посчитать количество уникальных Клиентов в связке с Calculate где Cumulative % <=1% (или decimal 0.01).
2 это 2%, система должна посчитать количество уникальных Клиентов с связке с Calculate где Culumative % <=2% (или decimal 0.02).
...
100 это 100%, система должна посчитать количество Клиентов в связке с Calculate где Cumulative % <=100% (или decimal 1).

Пишу на DAX STUDIO. Пока набросал следующую структуру:
Код
DEFINE
    MEASURE Sales[ClientsQty] =
        DISTINCTCOUNT ( Sales[Company] )
    MEASURE Sales[DistinctcountClients] =[COLOR=#ff0000] [/COLOR][COLOR=#ff0000]"???"[/COLOR]
EVALUATE
ADDCOLUMNS (
    SELECTCOLUMNS ( CALENDAR ( 1, 100 ), "Top %", INT ( [Date] ) ),
    "Clients Qty", [DistinctcountClients]
)

Скрин модели данных в приложении.
Скрытый текст

Пожалуйста, помогите с идеей

Технически я конечно могу написать:
Код
DEFINE
    MEASURE Sales[ClientsQty] =
        CALCULATE(DISTINCTCOUNT ( Sales[Company] ), Sales[Cumulative %]<= 0.01)

Но это надо делать 100 раз в таком случае (если шаг 1%), кроме того, таблица будет расти в горизонтальной плоскости, а не в вертикальной, как надо.

Вот еще одна идея как выгрузить рэнж от 0,01 до 1 (1-100%).
Код
EVALUATE
FILTER(SELECTCOLUMNS ( CALENDAR ( 0, 100 ), "Value", CURRENCY ( [Date] / 100 ) ), [Value]<>0)

Может поможет. Спасибо.
 
 
 
#2
27.09.2020 16:55:28
Технически я конечно могу написать:
Код
DEFINE
    MEASURE Sales[ClientsQty] =
        CALCULATE(DISTINCTCOUNT ( Sales[Company] ), Sales[Cumulative %]<= 0.01)

Но это надо делать 100 раз в таком случае (если шаг 1%), кроме того, таблица будет расти в горизонтальной плоскости, а не в вертикальной, как надо.

Вот еще одна идея как выгрузить рэнж от 0,01 до 1 (1-100%).
Код
EVALUATE
FILTER(SELECTCOLUMNS ( CALENDAR ( 0, 100 ), "Value", CURRENCY ( [Date] / 100 ) ), [Value]<>0)

Может поможет. Спасибо.
 
 
 
#3
27.09.2020 17:10:40
Цитата
Anatoly Vishnevsky написал:
Вот еще одна идея
У вас файл, вам и карты в руки
НА МЯСО В ПРОФИЛЕ
 
 
 
#4
27.09.2020 20:28:10
Михаил Л, не понятны Ваши комментарии. Если знаете как помочь, подскажите. Нет - прошу игнорировать пост. Карты у меня, но я их свечу, потому что не знаю как играть в данном случае. Прошу знающих людей подсказать.
Спасибо.
 
 
 
#5
27.09.2020 20:33:39
а формулами пойдет?
 
 
 
#6
27.09.2020 20:35:45
Цитата
Anatoly Vishnevsky написал:
Михаил Л , не понятны Ваши комментарии
здесь помогают по екселю и файлы соответствующего расширения должны быть, а не картинки - эо очень сильно ускорит получение Вами решения и думаю не одного
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#7
27.09.2020 21:45:19
Михаил Л, TokarevP, Mershik, спасибо, что откликнулись.
Вот материалы во вложении.

1. Сперва создал Столбец с последовательностью от 1 до 100 с шагом 1. Это будут %. Использовал меру:
FILTER(SELECTCOLUMNS ( CALENDAR ( 0, 100 ), "Value", CURRENCY ( [Date] / 100 ) ), [Value]<>0)

2. Далее через ADDCOLUMNS необходимо добавить "Distinct Company" столбик и считать DISTINCTCOUNT ( Sales[Company] ) c правилом по вышеуказанным %-ам. Скажем:
2.1. для первой строки это будет Sales[Cumulative %] <= 0.01 (1%)
2.2. для второй строки это будет Sales[Cumulative %] <= 0.02 (2%), ну и т.д.

Перебрал SUMX-образные меры, но не могу подобрать ключик.

Пожалуйста, помогите.
Изменено: Anatoly Vishnevsky - 27.09.2020 21:55:28
 
 
 
#8
28.09.2020 07:53:16
Попробуйте вот такую массивную формулу:
=СУММ(ЕСЛИОШИБКА(1/СЧЁТЕСЛИМН(SalesData[Client_ID];SalesData[Client_ID];SalesData[Cumulative %];"<="&Sales4[@Value]);))
 
 
 
#9
28.09.2020 08:25:39
Доброе время суток.
Анатолий, может потратить силы и время на то, чтобы описать что и где нужно сделать, включая пример результата в файле, чем объяснять путь решения, которое не приводит только к вам одному известному требуемому состоянию (вычисляемый столбец. Мера, тогда на каких измерениях)?
Изменено: Андрей VG - 28.09.2020 08:26:06
 
 
 
#10
28.09.2020 09:43:11
Андрей VG, добрый день.

Необходимо, чтоб система двигалась по столбцу от 0,01 до 1 и в соседнем столбце считала количество уникальных записей Клиентов (Distinctcount) по строчно от 0,01 до 1 (1-100%).

Пожалуйста, подскажите, что не так я объяснил. Спасибо и хорошего дня.
 
 
 
#11
28.09.2020 10:03:08
Ну, раз вы считаете, что всё понятно, то как понял :) как и у Светлого.
Updated
Чуток не докрутил
Код
=IF(HASONEVALUE('Sales4'[Value]);
    CALCULATE(DISTINCTCOUNT('Sales'[Client_ID]); FILTER('Sales'; 'Sales'[Cumulative %] <= VALUES('Sales4'[Value])));
    BLANK()
)
Изменено: Андрей VG - 28.09.2020 10:16:07
 
 
 
#12
28.09.2020 11:33:16
Андрей VG, большое Вам спасибо, но дело в том, что я пишу на DaxStudio и выгружаю в Excel уже готовый результат.
Вот, что я написал:
Код
DEFINE
    TABLE EffectiveRange = FILTER (
        SELECTCOLUMNS ( CALENDAR ( 0, 100 ), "Value", CURRENCY ( [Date] / 100 ) ),
        [Value] <> 0
    )
    MEASURE Sales[DistinctClient] = 1
EVALUATE
ADDCOLUMNS ( EffectiveRange, "Distinct", [DistinctClient] )

Помогите трансформировать это в запрос, а не свод, завершить меру выделенную выше (вместо 1).

По-хорошему, надо модернизировать формулу Distinctcount, чтоб она принимала значения столбика каждый раз:
Код
DEFINE
    TABLE EffectiveRange = FILTER (
        SELECTCOLUMNS ( CALENDAR ( 0, 100 ), "Value", CURRENCY ( [Date] / 100 ) ),
        [Value] <> 0
    )
    MEASURE Sales[DistinctClient] =
        DISTINCTCOUNT ( Sales[Company] )
EVALUATE
ADDCOLUMNS ( EffectiveRange, "Distinct", [DistinctClient] )

Спасибо.
Изменено: Anatoly Vishnevsky - 28.09.2020 11:43:32
 
 
 
#13
28.09.2020 18:13:02
Ребята, помогите.
 
 
 
#14
28.09.2020 19:55:21
Цитата
Anatoly Vishnevsky написал:
что я пишу на DaxStudio
И что, тут есть какая-то большая особенность? Ровно всё тоже :)
Код
EVALUATE
VAR from1to100 = CALENDAR ( 0, 100 )
VAR definePct = SELECTCOLUMNS (
        ADDCOLUMNS ( from1to100, "pct", [Date] / 100 ),
        "Percent", [pct]
    )
VAR defineDistinctCount = ADDCOLUMNS (
        definePct,
        "distinctCount",
            CALCULATE (
                DISTINCTCOUNT ( SalesData[Client_ID] ),
                FILTER (
                    SalesData,
                    SalesData[Cumulative %] <= [Percent] 
                )
            )
    )
RETURN defineDistinctCount
 
 
 
#15
29.09.2020 06:59:51
Цитата
Anatoly Vishnevsky
Пользователь
Сообщений: 15
Баллов: 0
Регистрация: 10 авг 2020
Последний визит: 28 сен 2020 21:56:55
Цитата
Anatoly Vishnevsky написал:
помогите.
и тишина :)
 
 
 
#16
29.09.2020 09:04:18
Андрей VG, обязательно отпишусь чуть позже. Ваш совет принял. Похоже, что Вы писали код в BI. Должен апробировать его с учетом правил DAX Studio, там нет возможности работать с VAR. Спасибо Вам и хорошего дня.  
 
 
 
#17
29.09.2020 09:14:47
Цитата
Anatoly Vishnevsky написал:
там нет возможности работать с VAR
Кто вам сказал такую глупость? И в надстройке DAX Studio для Excel 2016, и в надстройке для Excel 365 (где и писал) есть такая возможность. Так что шлите своего консультанта лесом свежим воздухом дышать :)
 
 
 
#18
29.09.2020 10:28:06
Андрей VG, я танцую, все работает! Вам большое спасибо! Просто супер! Хорошего дня!
 
 
 
Страницы: 1
Наверх