In this blog post, I want to share how I try to write DAX measures in order to show a list of customers who made TOP 50% of sales in each month.
One month, there might be 2 customers who made top 50% of sales of the month, whereas in the other month, there might be 5 customers who generated top 50% of sales of the month.
I created a simple sample like below, and let's start.
By the way, DAX measures written in this writing, if you twist a little bit (only change 0.5 to 0.8 and that is it), also help to find Top 80% customers, which is describing Pareto Analysis.
Sales table
Data model
I plan to create DAX measures like below.
Step one: I will show Sales by customer by month
Step two: I will describe each customer's percentage of monthly sales on each month.
Step three: I will try to cumulate the percentage from top percentage to bottom percentae on each month.
Step four: I will only show customers who are in the range of less than 50% of the result of step three.
The below explains what I wrote in DAX Studio in four steps that are mentioned above.
Step one:
Step two and three: when writing [Cumulate Percentage] measure, the cumulate is defined not by order-by-date, but by order-by-highest percentage to lowest percentage. This is the reason why VAR _percent = [Sales percent by customer by month] is written in the measure.
Step four: Just adding filter condition that [@cumulatepercentag] is less than or equal to 0.5. And it shows the list of customers per month with sales amount and ratio of sales amout per month. As shown below, some months show four customers who made top50% sales, while other months has two customers who generate top50% sales.
[Cumulate Percentage] measure in the below is the most important measure to get a solution, and rest are supporting it.
I hope the above formula written in Dax Studio can help you have fun in writing several supporting measures to come up with a final solution.
Comments