Objective:
We would always like to retain profitable customers, Those who give High Margin. And If there are buying less discounted items, that would be best.
Divide customer into 4 quadrants based on Margin % and Discount %
Best Customer – High Margin, Less Discount
Good Customer – High Margin, High Discount
Bad Customer – Low Margin, Less Discount
Worst Customer – Low Margin, High Discount
Implementation
We have 4 tables – Sales, Customer, Geography and Item
Create a date table and joined the salesdate of the sales table with the Date of Date.
Important Measures
Discount % = DIVIDE(Sum(Sales[Discount]),sum(Sales[Gross Sales]))
Margin % = DIVIDE([Margin Amount],sum(Sales[net sales]))
Now what does the above definition means in term of these measures
Best Customer – Margin% >= Over All Margin % , Discount % <= Over All Discount %
Good Customer – Margin% >= Over All Margin % , Discount % > Over All Discount %
Bad Customer – Margin% < Over All Margin % , Discount % <= Over All Discount %
Worst Customer – Margin% < Over All Margin % , Discount % < Over All Discount %
We would plot these customers on the scatter visual and color based on the above logic. So let us have a color measure
Color Dot =
var _avgDisc = CALCULATE([Discount %],ALL(Sales))
var _avgMargin = CALCULATE([Margin %],ALL(Sales))
return switch(TRUE(),
[Margin %]>=_avgMargin && [Discount %] <=_avgDisc , "Green",
[Margin %]>=_avgMargin && [Discount %] >_avgDisc , "Blue",
[Margin %]<_avgMargin && [Discount %] <=_avgDisc , "Yellow",
[Margin %]<_avgMargin && [Discount %] >_avgDisc , "Red", "Black")
The final visual
Let us know what you think about these measures. Share your thoughts on Customer Retention in Part 4.
The file can found at https://community.powerbi.com/t5/Quick-Measures-Gallery/Customer-Retention-Part-4-Customers-to-Retai…
You can get all my posts at https://community.powerbi.com/t5/Data-Stories-Gallery/Blog-Analysis/m-p/1265567#M4403
For a more detailed understanding of Margin discount Clustering in Customer Retention, you can check out the webinar. You can also follow my channel (YouTube) and LinkedIn (profile) to get information on the upcoming webinars.
Be the first to comment