Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-yiruan-msft
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 

v-yiruan-msft
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.