Create client's cohort by its transactions!

[Ustinovdl]

Hello!

I need help!

I have lot's of data like this:

PAYED_OTHERS

PAYED_E_TICKETS

PAYED_MOBILE

PAYED_P2P

PAYED_UTILITIES

PAYED_ADDITIONAL_SERVICES

PAYED_DISTRIBUTORS

PAYED_TELECOMMUNICATIONS

PAYED_MONTHLY_FEE

PAYED_E_GOV

BUSINESS_TYPE

USER_ID

LOGGED_IN

ANONYMOUS

R_TYPE

CHARGE_MONTHLY_FEE

TRANSACTIONS

TURNOVER

N

N

N

N

Y

N

N

N

N

N

B2B

1141

1

0

Y

Y

1

342,67

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1143

1

0

Y

N

1

9,9

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1143

1

0

Y

N

3

593,64

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1143

1

0

Y

N

3

309,1

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1143

1

0

Y

N

2

310

N

N

N

N

N

N

N

Y

N

N

B2B

1161

1

0

Y

Y

2

235,5

N

N

Y

N

Y

N

N

N

N

N

B2C

1212

1

0

Y

Y

2

4561,81

N

N

Y

N

Y

N

N

N

N

N

B2C

1212

1

0

Y

Y

5

250

N

N

Y

N

N

N

N

N

N

N

B2B

1222

1

0

Y

Y

1

50

N

N

Y

N

N

N

N

N

N

N

B2B

1263

1

0

Y

Y

1

50

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1356

1

0

Y

Y

3

2141,31

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1356

1

0

Y

Y

4

1100

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1356

1

0

Y

Y

1

150

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1356

1

0

Y

Y

1

9,9

N

N

Y

Y

Y

N

N

Y

Y

N

B2C

1469

1

0

Y

Y

3

321,75

N

N

Y

Y

Y

N

N

Y

Y

N

B2C

1469

1

0

Y

Y

3

650

N

N

Y

Y

Y

N

N

Y

Y

N

B2C

1469

1

0

Y

Y

1

171

N

N

Y

Y

Y

N

N

Y

Y

N

B2C

1469

1

0

Y

Y

3

5892

I want to analyze data by created cohorts by measures on turnover and transactions.

My logic is:

Sum transactions and turnover on every client + selected filters even if the user_id field unselected (maybe values dax function)

After that I would like to create fields like Transaction cohort and Turnover cohort like this:

Transaction cohort = switch(True();[Transactions by client] = 1;"1";
[Transactions by client] = 2;"2";
[Transactions by client] = 3;"3";
[Transactions by client] = 4;"4";
[Transactions by client] = 5;"5";
[Transactions by client] = 6;"6";
[Transactions by client] = 7;"7";
[Transactions by client] = 8;"8";
[Transactions by client] = 9;"9";
"10+")

and the same for Turnover by client.

But it doesn't work!!!! User_id is on the every transaction cohort if I have the row for this client with that value of transactions! How can I first of all calculated correct sum of transactions and then put the client in the correct cohort?!

[Ustinovdl]

I found out a solution but got another problem.

the solution is:

Transactions by client = CALCULATE([Transactions];FILTER(Table;table[CLIENT_ID]))

Transaction cohort = switch(True();[Transactions by client] = 1;"1";
[Transactions by client] = 2;"2";
[Transactions by client] = 3;"3";
[Transactions by client] = 4;"4";
[Transactions by client] = 5;"5";
[Transactions by client] = 6;"6";
[Transactions by client] = 7;"7";
[Transactions by client] = 8;"8";
[Transactions by client] = 9;"9";
"10+")

when I tried to add the same cohort column by turnover I got an error: A circular dependency was detected

I was wrong! It doesn't work

[v-huizhn-msft]

Hi @Ustinovdl,

How so you  add the same cohort column by turnover? It'd hard to reproduce without sample data. Could you please share your .pbix file for further analysis.

Best Regards,
Angelia

[Ustinovdl]

I was wrong. It doesn't work.

I don't know how can I calculate sum of transactions for every client including selected filters and then create a cohort column (

[v-huizhn-msft]

Hi @Ustinovdl,

It's hard to reproduce your scenario. What's formula of [Transactions] in your first reply.

Thanks,
Angelia

[Ustinovdl]

Transactions = sum(table[Transactions.])

[Ustinovdl]

Hello!

I need help!

I have lot's of data like this:

PAYED_OTHERS

PAYED_E_TICKETS

PAYED_MOBILE

PAYED_P2P

PAYED_UTILITIES

PAYED_ADDITIONAL_SERVICES

PAYED_DISTRIBUTORS

PAYED_TELECOMMUNICATIONS

PAYED_MONTHLY_FEE

PAYED_E_GOV

BUSINESS_TYPE

USER_ID

LOGGED_IN

ANONYMOUS

R_TYPE

CHARGE_MONTHLY_FEE

TRANSACTIONS

TURNOVER

N

N

N

N

Y

N

N

N

N

N

B2B

1141

1

0

Y

Y

1

342,67

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1143

1

0

Y

N

1

9,9

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1143

1

0

Y

N

3

593,64

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1143

1

0

Y

N

3

309,1

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1143

1

0

Y

N

2

310

N

N

N

N

N

N

N

Y

N

N

B2B

1161

1

0

Y

Y

2

235,5

N

N

Y

N

Y

N

N

N

N

N

B2C

1212

1

0

Y

Y

2

4561,81

N

N

Y

N

Y

N

N

N

N

N

B2C

1212

1

0

Y

Y

5

250

N

N

Y

N

N

N

N

N

N

N

B2B

1222

1

0

Y

Y

1

50

N

N

Y

N

N

N

N

N

N

N

B2B

1263

1

0

Y

Y

1

50

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1356

1

0

Y

Y

3

2141,31

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1356

1

0

Y

Y

4

1100

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1356

1

0

Y

Y

1

150

N

N

Y

N

Y

N

N

Y

Y

N

B2C

1356

1

0

Y

Y

1

9,9

N

N

Y

Y

Y

N

N

Y

Y

N

B2C

1469

1

0

Y

Y

3

321,75

N

N

Y

Y

Y

N

N

Y

Y

N

B2C

1469

1

0

Y

Y

3

650

N

N

Y

Y

Y

N

N

Y

Y

N

B2C

1469

1

0

Y

Y

1

171

N

N

Y

Y

Y

N

N

Y

Y

N

B2C

1469

1

0

Y

Y

3

5892

I want to analyze data by created cohorts by measures on turnover and transactions.

My logic is:

Sum transactions and turnover on every client + selected filters even if the user_id field unselected (maybe values dax function)

After that I would like to create fields like Transaction cohort and Turnover cohort like this:

Transaction cohort = switch(True();[Transactions by client] = 1;"1";
[Transactions by client] = 2;"2";
[Transactions by client] = 3;"3";
[Transactions by client] = 4;"4";
[Transactions by client] = 5;"5";
[Transactions by client] = 6;"6";
[Transactions by client] = 7;"7";
[Transactions by client] = 8;"8";
[Transactions by client] = 9;"9";
"10+")

and the same for Turnover by client.

But it doesn't work!!!! User_id is on the every transaction cohort if I have the row for this client with that value of transactions! How can I first of all calculated correct sum of transactions and then put the client in the correct cohort?!