Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
SMTHRB
New Member

Need Help calculating customer lifetime (total giving) to create customer categories

Hello, I am new to PowerBi and need  help creating a table that show the customer ID, the total amount the customer has spent, and then a third column that identifies the customer category based on the customer total giving (over $600 category 1, between 200 and 600 category 2, between 100 and 200 category 3, etc.)

 

The starting data looks like the first table shown below. My end goal is to be able to create a new table that has one row per customer showing the customer ID, the total amount spent by the customer, and a category group. I just need the total spent on the row level unchanged so that I can create a new column to create the categories.

SMTHRB_0-1658250070593.png

 

Final table should look like this:

SMTHRB_1-1658250344057.png

 

Any suggestions would be greatly appreciated. Thank you so much in advance!

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @SMTHRB ,

 

Please try:

First, create a new table:

 

Table 2 = SUMMARIZE('Table',[Customer ID],"Customer Lifetime Amount",SUM('Table'[Amount]))

 

Output:

vjianbolimsft_0-1658474448249.png

 

Then add a new column:

 

Customer Category = SWITCH(TRUE(),[Customer Lifetime Amount]>=600,"Category 1",[Customer Lifetime Amount]<600&&[Customer Lifetime Amount]>=200,"Category 2",[Customer Lifetime Amount]<200&&[Customer Lifetime Amount]>=100,"Category 3","Category 4")

 

Final output:

vjianbolimsft_1-1658474448252.png

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-jianboli-msft
Community Support
Community Support

Hi @SMTHRB ,

 

Please try:

First, create a new table:

 

Table 2 = SUMMARIZE('Table',[Customer ID],"Customer Lifetime Amount",SUM('Table'[Amount]))

 

Output:

vjianbolimsft_0-1658474448249.png

 

Then add a new column:

 

Customer Category = SWITCH(TRUE(),[Customer Lifetime Amount]>=600,"Category 1",[Customer Lifetime Amount]<600&&[Customer Lifetime Amount]>=200,"Category 2",[Customer Lifetime Amount]<200&&[Customer Lifetime Amount]>=100,"Category 3","Category 4")

 

Final output:

vjianbolimsft_1-1658474448252.png

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@SMTHRB , This needs dynamic segmentation using measure and an independent table.

I have done it for the margin column, check if you can use that

 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.