cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bilalkhokar73
Helper IV
Helper IV

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

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_0-1666764572806.png

 

 

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

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.

yingyinr_0-1666858126757.png

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"

yingyinr_1-1666858961031.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
bilalkhokar73
Helper IV
Helper IV

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 

yingyinr
Community Support
Community Support

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.

yingyinr_0-1666858126757.png

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"

yingyinr_1-1666858961031.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.