Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
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
Hi @Anonymous ,
This can be solved using percentiles.
Create 2 measures.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
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 ,
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
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
@Anonymous
You can create a new table and sort by total amount
Table = SUMMARIZECOLUMNS(tbl[Customer name],"total amount",sum(tbl[Amount]))
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
Proud to be a Super User!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |