Tìm các sản phẩm có doanh thu cao nhất theo từng nhóm hàng (sử dụng Power Query) (1 người xem)

[lehuong1409]

Chào các anh chị ạ
Hiện em có dữ liệu như file đính kèm

Dựa vào cột doanh thu em muốn tìm theo từng nhóm mỗi nhóm một vài mặt hàng có doanh thu lớn nhất, Số lượng mặt hàng cần lấy tại mỗi nhóm là khác nhau


Em đã giải bài toán này bằng hàm và cũng có tìm hiểu để thử giải bằng Power Query nhưng đoạn COUNTIFS với điều kiện >= doanh thu thì em chưa tìm được giải pháp. Kính mong anh chị em trong diễn đàn hỗ trợ em giải quyết bài toán này bằng Power Query ạ. Em xin chân thành cảm ơn

 

[ptm0412]

Dùng code sau và không cần cột D:

let  
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    GrNum = {0..List.Count(TOP[TOP])-1},    
    DataN = List.Transform (GrNum, (n) =>
    let
        Filter = Table.SelectRows(Source, each ([Nhóm sản phẩm] = TOP[Nhóm]{n})),
        Sort = Table.Sort(Filter,{{"Doanh thu", Order.Descending}}),
        TopN = Table.FirstN(Sort,TOP[TOP]{n}),
        AddRank = Table.AddIndexColumn(TopN, "Thứ hạng", 1, 1, Int64.Type)
    in
        AddRank),
    Result =  Table.Combine(DataN)
in
    Result

 

[lehuong1409]

Dùng code sau và không cần cột D:

let 
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    GrNum = {0..List.Count(TOP[TOP])-1},   
    DataN = List.Transform (GrNum, (n) =>
    let
        Filter = Table.SelectRows(Source, each ([Nhóm sản phẩm] = TOP[Nhóm]{n})),
        Sort = Table.Sort(Filter,{{"Doanh thu", Order.Descending}}),
        TopN = Table.FirstN(Sort,TOP[TOP]{n}),
        AddRank = Table.AddIndexColumn(TopN, "Thứ hạng", 1, 1, Int64.Type)
    in
        AddRank),
    Result =  Table.Combine(DataN)
in
    Result

View attachment 301299

Hiện cháu thực hiện và đang báo lỗi như sau:

Cháu có hướng rồi cháu sẽ tìm hiểu để hoàn thành với đề bài của mình ạ.
Cột D sử dụng khi cháu dùng hàm để ra được đáp án ạ. Cháu cảm ơn chú ạ

 

[hoamattroicoi]

Hiện cháu thực hiện và đang báo lỗi như sau:
View attachment 301302
Cháu có hướng rồi cháu sẽ tìm hiểu để hoàn thành với đề bài của mình ạ.
Cột D sử dụng khi cháu dùng hàm để ra được đáp án ạ. Cháu cảm ơn chú ạ

Em phải load cả cái table TOP lên mới được ý.
Bảng này nhé.

 

[ptm0412]

Hiện cháu thực hiện và đang báo lỗi như sau:

Do tôi đổi tên Tìm_TOP thành TOP đó. Sửa lại thành TOP thì query tự sửa theo á.

Em phải load cả cái table TOP lên mới được ý.
Bảng này nhé.

Bảng Tìm_TOP có sẵn rồi Còi, chỉ đổi tên thôi.

 

[tranhungdao12a3]

Bạn có thể thử giải pháp Dax sau:

 

[lehuong1409]

Dạ cháu/em đã sửa và làm được rồi ạ. Cháu cảm ơn chú @ptm0412 và chị @hoamattroicoi ạ

Bài đã được tự động gộp: 2/6/24

Bạn có thể thử giải pháp Dax sau:
View attachment 301329

Em cảm ơn anh đã phản hồi ạ, Cột check em tạo ra để làm hàm nên sử dụng trên cột Check thì em chỉ cần lọc là có kết quả rồi ạ. Query em mới tập sự nên còn phải học thêm nhiều ạ

 

[tranhungdao12a3]

Bạn có thể Load Table TopN đó lên, sau đó Ranking theo TopN: Nếu cần Tùy biến Top thì sửa nguồn tại bảng TopN rồi Refresh là được:

 

[hocexcel_1991]

Gửi bạn thêm cách, nếu đồng hạng thì lấy luôn.

let
    doanhthu = Excel.CurrentWorkbook(){[Name="doanhthu"]}[Content],
    top = Excel.CurrentWorkbook(){[Name="top"]}[Content],
    ket_qua = Table.Combine(Table.Group(doanhthu,"Nhóm sản phẩm",{"All", (tbl)=> Table.SelectRows(Table.AddRankColumn(tbl,"Check",{"Doanh thu",1},[RankKind = 1]), each [Check]<= top{[Nhóm=[Nhóm sản phẩm]]}[TOP])})[All])
in
    ket_qua

 

[lehuong1409]

Bạn có thể Load Table TopN đó lên, sau đó Ranking theo TopN: Nếu cần Tùy biến Top thì sửa nguồn tại bảng TopN rồi Refresh là được:
View attachment 301337

Em đã tìm hiểu và làm theo được cách này ạ. Nhưng hiện tại hàm SELECTEDVALUE của em không có, em có tham khảo và dùng MIN MAX thay thế và đạt kết quả như mong muốn rồi ạ. Cảm ơn anh đã chia sẻ thêm một cách giải quyết vấn đề của em ạ. Em cảm ơn

Bài đã được tự động gộp: 3/6/24

Gửi bạn thêm cách, nếu đồng hạng thì lấy luôn.

let
    doanhthu = Excel.CurrentWorkbook(){[Name="doanhthu"]}[Content],
    top = Excel.CurrentWorkbook(){[Name="top"]}[Content],
    ket_qua = Table.Combine(Table.Group(doanhthu,"Nhóm sản phẩm",{"All", (tbl)=> Table.SelectRows(Table.AddRankColumn(tbl,"Check",{"Doanh thu",1},[RankKind = 1]), each [Check]<= top{[Nhóm=[Nhóm sản phẩm]]}[TOP])})[All])
in
    ket_qua

Chào bạn, Cột Check mình đưa ra chỉ dùng khi mình chạy ra kết quả bằng công thức. Còn nếu sử dụng Power Query thì mình không muốn có thêm cột Check đó nữa. Cảm ơn bạn đã hỗ trợ

 

[hocexcel_1991]

Em đã tìm hiểu và làm theo được cách này ạ. Nhưng hiện tại hàm SELECTEDVALUE của em không có, em có tham khảo và dùng MIN MAX thay thế và đạt kết quả như mong muốn rồi ạ. Cảm ơn anh đã chia sẻ thêm một cách giải quyết vấn đề của em ạ. Em cảm ơn

Bài đã được tự động gộp: 3/6/24

Chào bạn, Cột Check mình đưa ra chỉ dùng khi mình chạy ra kết quả bằng công thức. Còn nếu sử dụng Power Query thì mình không muốn có thêm cột Check đó nữa. Cảm ơn bạn đã hỗ trợ

Cột "Check" đó không liên quan đến cột "Check" mà bạn tạo ở excel, nó là cột "Rank". Bạn ko muốn nó hiển thị thì thêm step xóa nó đi là được.

 

[lehuong1409]

Bạn gửi lại giúp mình file bạn đã hỗ trợ tạo được không bạn? Vì mình chạy ra thì vẫn bị báo lỗi, mình tìm sửa chưa được nên thấy giá trị Check lại hiểu nhầm là bạn lấy thông tin ở cột Check. Cảm ơn bạn

 

[hocexcel_1991]

Bạn gửi lại giúp mình file bạn đã hỗ trợ tạo được không bạn? Vì mình chạy ra thì vẫn bị báo lỗi, mình tìm sửa chưa được nên thấy giá trị Check lại hiểu nhầm là bạn lấy thông tin ở cột Check. Cảm ơn bạn

Mình đình kèm file lại ở #9 rồi nhé

 

[DeTong]

...Mình đình kèm file lại ở #9 rồi nhé...

Báo lỗi này thì khắc phục làm sao ạ!

 

[hocexcel_1991]

Báo lỗi này thì khắc phục làm sao ạ!

Bạn dùng office bản nào? Lỗi không có hàm đó, mình dùng office365

 

[DeTong]

Lỗi không có hàm đó

Office 2019 chắc chưa có hàm này...

 

[ptm0412]

Học được hàm Table.AddRankColumn, nhờ nó lấy được đồng hạng mà không phải sort

let  
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    GrNum = {0..List.Count(TOP[TOP])-1},    
    DataN = List.Transform (GrNum, (n) =>
    let
        Filter = Table.SelectRows(Source, each ([Nhóm sản phẩm] = TOP[Nhóm]{n})),
        AddRank = Table.AddRankColumn(Filter,"Thứ hạng",{"Doanh thu",1},[RankKind = 1]),
        TopN = Table.SelectRows(AddRank,each [Thứ hạng] <= TOP[TOP]{n})
    in
        TopN),
    Result =  Table.Combine(DataN)
in
    Result

 

[ngocrin1]

Tập tành M code với mọi người

let
    Source = Excel.CurrentWorkbook(){[Name="SalesGroup"]}[Content],
    GroupCondition = Excel.CurrentWorkbook(){[Name="Group"]}[Content],
    GroupedRows = Table.Group(Source, {"Group"},
        {{"Details", each Table.AddRankColumn(_,"Rank",{"Amount",1})}}),
    Expand = Table.Combine(GroupedRows[Details]),
    Filter = Table.AddColumn(GroupCondition, "Table", each
        let
        crGroup = _[Group],
        crNo = _[TOP],
        filter = Table.SelectRows(Expand,each crGroup = [Group] and crNo>=[Rank])
        in filter
),
    Combine = Table.Combine(Filter[Table])
in
    Combine

 

[ptm0412]

Tập tành M code với mọi người

Cũng với dữ liệu trên, bạn thử lấy những dòng còn lại sau khi lấy Top như trên. Số nhỏ nhất có thứ hạng 1

 

[ngocrin1]

Cũng với dữ liệu trên, bạn thử lấy những dòng còn lại sau khi lấy Top như trên. Số nhỏ nhất có thứ hạng 1

Xem thử ổn ko anh.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesGroup"]}[Content],
    GroupCondition = Excel.CurrentWorkbook(){[Name="Group"]}[Content],
    GroupedRows = Table.Group(Source, {"Group"},
        {{"Details", each Table.AddRankColumn(_,"Rank",{"Amount",0})}}),
    Expand = Table.Combine(GroupedRows[Details]),
    findMaxCount = Table.Group(Source, {"Group"},
        {"Count", each Table.RowCount(_), Int64.Type}),
    MergeWMaxCount = Table.NestedJoin(GroupCondition, {"Group"}, findMaxCount, {"Group"}, "findMaxCount", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(MergeWMaxCount, "findMaxCount", {"Count"}, {"Count"}),
    AddRemain = Table.SelectColumns(Table.AddColumn(Expanded, "Remain", each [Count] - [TOP], type number),{"Group", "Remain"}),
    Filter = Table.AddColumn(AddRemain, "Table", each
        let
        crGroup = _[Group],
        crNo = _[Remain],
        filter = Table.SelectRows(Expand,each crGroup = [Group] and crNo>=[Rank])
        in filter
),
    Combine = Table.Combine(Filter[Table])
in
    Combine