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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Customer Segmentation based on Running Total & Threshold value

Hi All,

 

Task:
I have been tasked to create a measure which categories our customers in 3 groups namely Gold, Silver and Bronze. The measure should determine the group dynamically in the filter context. The categorization is based on cumulative/running total at customer level.

 

Data:
The data is at invoice and month level. So each customer can have multiple invoices in a month.

Screenshot Below:

Data.JPG

Steps to achieve this in Excel or on paper:
1. Create a measure of sum(invoice amount) and call it totalamount
2. Group by Customer name. So you have 2 columns --> Customer and totalamount
3. Sort it by totalamount desc (so highest inviced customer is on top)
3. Take Grand Total of totalamount column. Your threshold are based on this, Gold = 70% of totalamount and Silver = 80%
4. You now take running total of the totalamount.
5. When your running total reaches the 70% of the Grand Total or the Gold mark you stop and all Customer before that are Gold customer, then you continue that and at 80% of the Grand Total, all customers between 70 - 80 are Silver and remaining Bronze.

 

Thus, we segment customer basis the Pareto principle, where 80% revenue is brought by 20% of customer. This will help us identify the customers on a monthly/weekly basis dynamically to target them accordingly.

 

Analysis/Output Screenshots:

analysis.JPG

Please suggest us some steps as to how to create a dynamic running total measure which can be referred in the var variable and be used dynamically.

 

Thanks.

--
Regards
Saurabha J

 

8 REPLIES 8
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

This can be solved using percentiles.

 

Create 2 measures.

 

Total Amount = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Customer Name]))
 
Segment =

var _70p = PERCENTILEX.EXC(ALL('Table'[Customer Name]),[Total Amount],.70)
var _60p = PERCENTILEX.EXC(ALL('Table'[Customer Name]),[Total Amount],.60)

return
SWITCH(
TRUE(),
[Total Amount] > _70p , "Gold",
[Total Amount] <= _70p && [Total Amount] > _60p , "Siver",
"Bronze"
)
 
 
1.jpg2.JPG
 
 
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 
Anonymous
Not applicable

Hi @harshnathani ,

 

We had tried with the PERCENTILE Method, but it doesnt work.

What PERCENTILE.EXC will do is in the list of all the Amounts, it will get us the 70th% or 60th% value ie if I have a range from 100 numbers, it will give me the 70th value.


We do not want the 70th value but the value where 70% of Grand total is met. It could be the 5th percentile or even 50th percentile. Hence we will need to consider the running/cumuliative sum of amount.

 

Please let me know if I am not understanding your solution or so.

 

Thanks for your prompt reply.

 

--

Regards

Saurabha

Hi @Anonymous ,

 

 

var _70p = .70* CALCULATE([Total Amount],ALL('Table'))
var _80p = .80* CALCULATE([Total Amount],ALL('Table'))
var _rank = RANKX(ALL('Table'[Customer Name]),[Total Amount])
var _runningtotal =CALCULATE([Total Amount],FILTER(ALL('Table'), _rank >= RANKX(ALL('Table'[Customer Name]),[Total Amount])))
return

SWITCH(
TRUE(),
_runningtotal<= _70p , "Gold",
_runningtotal > _70p && _runningtotal <= _80p , "Siver",
_runningtotal >_80p, "Bronze"
)
 
Just change the measure Segment to the above in the file attached.
 
 
1.jpg
 
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous
Not applicable

Hi @harshnathani ,

 

Sorry for the delay in reply.

 

Your solution works on the overall data, since you have used ALL, ALLEXCEPT everywhere and partially.

But when I tried removing ALL/ALLEXCEPT, if did not work.

 

We want to make it dynamic, so if we select a particular "Location Name" or "Month", it will select only those Customers and Calculate thier SUM of Amounts basis the filters selected.

It will then dynamically calculate the Running Total and the Thresholds on that data and then show us thier Segmentionation(Gold, Silver,...) accordingly.

Thanks
Saurabha J

 

ryan_mayu
Super User
Super User

@Anonymous 

 

You can create a new table and sort by total amount

Table = SUMMARIZECOLUMNS(tbl[Customer name],"total amount",sum(tbl[Amount]))

1.PNG

Now the problem is I don't know how you get the running total.

If you can get that amount by using the same method.

you can try

column=
VAR percentage = total amount/ running total
return switch(true(),percentage<70%,"Gold",percentage <80%,"Silver","Bronze"

Something like that

It will be better if you can share the sample excel file

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi @ryan_mayu ,

 

We cannot create a table, as SUMMARIZE and SUMMARIZETABLE data is preloaded only once at model refresh and does not change dynamically on changes in Report Filter.
So we will have to calculate it on the fly in the measure itself.

 

Calculating Running total in the SUMMARIZE or SUMMARIZETABLE is actually the issue, that we are facing. If we can do that then, everything else should fall in place.

 

--

Regards

Saurabha

 

Hi Saurabha,

Finally, did you have a solution for your problem?  I have a similar problem and if you found a way to do it, please share it!

Thanks

 

Hi,

Share some data, describe the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.