cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors