Dynamically Segment Customers based on Criteria

[Josh97Ellis]

Hello, 

I have a transactional fact table based on invoices, and I am trying to segment the customers based on sales and margin for any time period that I want.

Sales Segment:

The sales segment will be based on total net sales for each customer during a given time period. 

High Sales: Greater than the 80th percentile of total sales for each customer.

Medium Sales: Between the 30th and 80th percentile of total sales for each customer

Low Sales: Less than the 30th percentile of total sales for each customer

Margin Segment

High Margin: Greater than 35%

Medium Margin: Between 15% and 35%

Low Margin: Less than 15% 

I have been able to *temporarily* figure this out, just to show what I am looking to accomplish. However, I just created a summary table of the customers by month, and created columns that took the sum of sales and contribution margin, which are divided to get my contribution margin %. I then created columns using an IF statement in order to segment my data, based on the above criteria. However, this method is not fully integrated with the main table and does not work exactly how I need it to. 

Unfiltered:

Filtered: High Sales and High Margin

Essentially, I am looking to group my customers from the invoiced sales table, and sum each customer's net sales and contribution margin amount to calculate the margin %, and then be able to filter on each segement group to define a segment to analyze. 

I am think I am going to have to create segment tables that define the minimum and maximum critera for each group, and then create a mesure in the fact table that will group and sumarize the table in relation to these segment tables. I could maybe then use the tables for my filters, and filtering on a measure is not possible. 

However, I have no idea how to approach this, there may be a much better way, and I might be overthinking everything. 

[Ashish_Mathur]

Hi,

I'd like to help though i am not sure of how much i will be able to.  Share the link from where i can download your pbi file.  Keep the file as simple as is possible with only those tables which are required for answering this question and those from where you would like to build slicers.  Ensure that you have written the measure for the percentile calculation in that file.