Top 100 Cusstomer , followed by 101-200 Top customer Month wise and Sicer wise selection

[bilalkhokar73]

I have here data of 2 month ,

 FIle - https://we.tl/t-2URX3OyRSv

I need that for Sept month i need  (If i select August month slicer then august month data shoudl come, if I select both month slicer then month wise data need)

A- Top 100 Customer sales mesaure

B-then after that 100 to 200 top sales measure (Here Mesaure A Customer  will not come in this mesaure)

C-200-300 Customer  sales 
@speedramps 
Sample in excel 

[bilalkhokar73]

NO, I donot want to divide with sales number
I want to divide data basis of Customer sales, let say I did sorting in excel highest to lowest customer wise sales, then I will select top 100  rows customer , then after 100 I want below 101-200 rows 

[v-yiruan-msft · Accepted solution]

Hi @bilalkhokar73 ,

I'm not clear about your requirement. Do you want to divide the data from the table "SalesTB" into multiple parts(Top 100, Top 200-300, Top 301-400 etc.)? But according to which field grouping to which numeric field to rank? Could you please provide more details on it? Thank you.

For the sample data in excel file, we can handle with it in Power Query Editor just like below. Please find the details in the attachment.

let
    Source = Excel.Workbook(File.Contents("XXXX\Sample data of ept month.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"customer", Int64.Type}, {"amnt", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"amnt", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Top", each if [Index]<=100 then "Top 100" 
else if [Index]>=101 and [Index]<=200 then "Top 101-200"
else if [Index]>=201 and [Index]<=300 then "Top 201-300"
else if [Index]>=301 and [Index]<=400 then "Top 301-400"
else if [Index]>=401 and [Index]<=500 then "Top 401-500"
else if [Index]>=501 and [Index]<=600 then "Top 401-500"
else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Best Regards