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

[SMTHRB]

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.

Final table should look like this:

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

[v-jianboli-msft · Accepted solution]

Hi @SMTHRB ,

Please try:

First, create a new table:

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

Output:

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:

^{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]

@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