Các tình huống liên quan đến dùng DAX để xử lý và phân tích dữ liệu (1 người xem)

[Quangdz0512]

Xin chào các anh, chị.
Em có tìm hiểu về PowerPivot và PowerBI (chủ yếu qua tài liệu mà chú Mỹ biên soạn) em thấy rất hay và ham. Tuy nhiên em khá lúng túng với việc dùng hàm DAX. Ở GPE có bác @excel_lv1.5 rất giỏi khoản này. Đôi khi em phải ngồi lần lại các bài viết của bác ấy để đọc và nghiền ngẫm. Nhưng do tư chất kém thông minh nên vẫn lơ mơ lắm.
Em thì không thiên về tạo các Visual lắm (do công việc ít khi phải dùng để trình bày PowerPoint), em rất mong muốn có thể áp dụng DAX để ra các thông tin phân tích và gán lại kết quả về bảng tính excel thông qua Pivot Table (power pivot của excel). Như bài tính số lượng mua hàng trung bình của 2 tháng mà bác @excel_lv1.5 đã giải.
Em mong muốn GPE tạo nhiều chủ đề DAX, nêu các tình huống cụ thể và bài giải như bài em đã ví dụ ở trên.
Rất cảm ơn các anh, chị.

Bài đã được tự động gộp: 16/8/23

Ví dụ bài này ạ

Tính trung bình 2 tháng liền kề trong Powerpivot

Nhờ anh chị giúp em viết hàm trong PowerPivot ,tính cột trung bình bán khi chọn tháng trong List Datavalidation,và cột tỷ lệ tính theo cột trung bình bán , Em có gửi theo file ,nhờ anh chị giúp đỡ .

www.giaiphapexcel.com

 

[tranhungdao12a3]

Xin chào các anh, chị.
Em có tìm hiểu về PowerPivot và PowerBI (chủ yếu qua tài liệu mà chú Mỹ biên soạn) em thấy rất hay và ham. Tuy nhiên em khá lúng túng với việc dùng hàm DAX. Ở GPE có bác @excel_lv1.5 rất giỏi khoản này. Đôi khi em phải ngồi lần lại các bài viết của bác ấy để đọc và nghiền ngẫm. Nhưng do tư chất kém thông minh nên vẫn lơ mơ lắm.
Em thì không thiên về tạo các Visual lắm (do công việc ít khi phải dùng để trình bày PowerPoint), em rất mong muốn có thể áp dụng DAX để ra các thông tin phân tích và gán lại kết quả về bảng tính excel thông qua Pivot Table (power pivot của excel). Như bài tính số lượng mua hàng trung bình của 2 tháng mà bác @excel_lv1.5 đã giải.
Em mong muốn GPE tạo nhiều chủ đề DAX, nêu các tình huống cụ thể và bài giải như bài em đã ví dụ ở trên.
Rất cảm ơn các anh, chị.

Bài đã được tự động gộp: 16/8/23

Ví dụ bài này ạ

Tính trung bình 2 tháng liền kề trong Powerpivot

Nhờ anh chị giúp em viết hàm trong PowerPivot ,tính cột trung bình bán khi chọn tháng trong List Datavalidation,và cột tỷ lệ tính theo cột trung bình bán , Em có gửi theo file ,nhờ anh chị giúp đỡ .

www.giaiphapexcel.com

Bạn hỏi vậy là tương đối rộng, không ai có nhiều thời gian rảnh rỗi để mở và theo chủ đề như vậy, thay vì như vậy bạn nên mở chủ đề mà bạn đang vướng, cần giải quyết trong công việc bằng Dax để các thành viên có thể giúp bạn.
Tôi giới thiệu sơ qua về Dax để bạn thấy ưu và nhược điểm của nó:
Dax(Data Analysis Expressions) là một công cụ phân tích, tính toán dữ liệu mạnh mẽ
Có thể phân loại Dax theo ba hướng như sau:
1. Tạo một biểu thức tính toán là một Measure, một biểu thức Measure luôn luôn trả về một giá trị vô hướng, một Scalar
2. Tính toán tạo một cột dữ liệu mới trong bảng: một calculated column tính toán dữ liệu cho một cột khi bạn tạo một cột mới trong table
3. Tạo một bảng tính toán: Calculated tables - Nó cho phép bạn tạo luôn một bảng kết quả mà bạn cần theo nhu cầu, do nó chạy trong Data model cho nên cái bảng này lại có thể relationship để liên kết với các bảng gốc đang có của dữ liệu để tùy biến phát triển như một bảng bình thường
Một vài ghi chú:
- Một measure như đã nói ở trên thì luôn trả về một giá trị vô hướng, nó sống trong visual, khi bạn kéo vào visual thì nó mới chạy
Khi bạn viết visual sẽ không làm tăng dung lượng file, nhưng nó sẽ chiếm rất nhiều Ram vì viết measure thì dùng rất nhiều bảng ảo trong đó(điều đó lý giải vì sao khi bạn dùng dax power pivot hay Power bi thì cần rất nhiều Ram cho nó), nó sẽ không có sẵn bối cảnh dòng(rowcontext) nhưng nó có sẵn bối cảnh bộ lọc( filter context)
- Một calculated tables: dữ liệu tính toán trả về cho cả một cột mới, do tạo cột mới trong tables cho nên nó sống trong tables, do nó là một cột vật lý cho nên nó sẽ làm tăng dung lượng của file, tăng kích thước bảng dữ liệu, nó sẽ chiếm nhiều CPU. Nó tạo trong bảng nên nó có sẵn bối cảnh hàng(rowcontext), không có sẵn bối cảnh bộ lọc(filter context)
Do Dax nó chạy trong một Data model, một cơ sở dữ liệu có quan hệ cho nên bạn có thể chạy với dữ liệu hàng triệu, chục triệu hay trăm triệu dòng vẫn được hết miễn là Ram bạn đủ lớn, 16GB ram thì cơ bản có thể chạy được 50 triệu dòng không vấn đề gì( tất nhiên còn tùy thuộc độ phức tạp của hàm khi bạn viết, hay kích thước số cột của các bảng nữa)
Muốn học Dax thì thứ quan trọng nhất là phải hiểu hai khái niệm Row context và filer context, hai cái này mới quan trọng chứ không phải hàm, vì hàm trong Dax đa phần nó gần giống hàm excel
Hàm trong Dax nó tính toán theo bảng, theo cột còn hàm trong excel nó tính toán theo ô bạn cần ghi nhớ khi tiếp cận nó.
Một ghi chú nữa là Row context không lan truyền qua bộ lọc, tức là từ bảng này mà bạn lấy qua bảng khác thì Row context nó không lan truyền
Ngược lại với Row context thì Filter context có lan truyền qua bộ lọc, Row context thì không có nhiều trường hợp lắm, chủ yếu là khi tạo cột mới, hoặc tính toán các hàm có đuôi X như Sumx, minx, maxx, averagex,....
Dax sử dụng ở đâu:
- Bạn có thể dùng nó trong Power pivot của excel: ưu điểm là có thể chạy luôn trên excel, nhược điểm là bị hạn chế một số thứ( như không có sử dụng mối quan hệ many to many được, các hàm mảng bị hạn chế, trực quan hóa bị hạn chế,...)
- Bạn có thể dùng nó trong Power Bi là một tool riêng
Ngoài ưu điểm tính toán mạnh mẽ, Dax có bộ hàm Time Intelligence, nó giúp cho bạn khi phân tích hay so sánh dữ liệu thời gian ngày, tuần, tháng, quý, năm,... một cách mạnh mẽ
Tôi giới thiệu cơ bản để bạn hiểu về Dax khi tiếp cận nó!

 

[Quangdz0512]

Chào các anh, chị.
Em có tình huống như thế này. Muốn giải bằng cả 2 cách là PowerQuery và DAX
Em có 2 bảng, 1 bảng là thông tin mua hàng, 1 bảng là đơn giá Định mức của từng sản phẩm.
1. Em muốn tìm ra Tổng số tiền nhân viên mua hàng theo Sản phẩm. Và so sánh với định mức
2. Cũng tương tự như vậy nhưng có thêm điều kiện Chiết khấu (từ sản phẩm thứ 4 trở lên được giảm 5%)
3. Tìm đơn giá trung bình theo tỉnh thành để so sánh với Định mức. Từ đó xem xét xem có phải ban hành lại Định mức hay không
Chi tiết dữ liệu đính kèm
Em cảm ơn!

 

[tranhungdao12a3]

Chào các anh, chị.
Em có tình huống như thế này. Muốn giải bằng cả 2 cách là PowerQuery và DAX
Em có 2 bảng, 1 bảng là thông tin mua hàng, 1 bảng là đơn giá Định mức của từng sản phẩm.
1. Em muốn tìm ra Tổng số tiền nhân viên mua hàng theo Sản phẩm. Và so sánh với định mức
2. Cũng tương tự như vậy nhưng có thêm điều kiện Chiết khấu (từ sản phẩm thứ 4 trở lên được giảm 5%)
3. Tìm đơn giá trung bình theo tỉnh thành để so sánh với Định mức. Từ đó xem xét xem có phải ban hành lại Định mức hay không
Chi tiết dữ liệu đính kèm
Em cảm ơn!

Của bạn như sau, hình ảnh kết quả đây:

 

[Quangdz0512]

Của bạn như sau, hình ảnh kết quả đây:
View attachment 293996

Cảm ơn anh @tranhungdao12a3
Em gửi bổ sung cách tính bằng PowerQuery, bài này em hoàn toàn dùng các bước của PowerQuery thôi, chưa đạt đến trình độ gõ trực tiếp vào Advanced Editer được.
Theo bài giải của anh thì phần Tổng tiền mua sau Chiết khấu anh đang hiểu nhầm 1 chút là cứ mua đơn hàng số lượng > 3 là đơn giá 95%. Tuy nhiên đề bài là chỉ Chiết khấu cho các Sản phẩm có số lượng mua từ 4 trở lên.
Em có 3 nội dung muốn hỏi thêm anh.
1. Trong DAX có cách nào lọc trước để những dòng không có kết quả thì không cho hiện ra được không?
Ví dụ bảng kết quả số 3, nhiều SP không mua nên không có đơn giá Trung bình theo tỉnh, nhưng vẫn mang đi so sánh với đơn giá định mức
2. Với bảng dữ liệu này, nếu đưa vào Pivot Chart thì các dòng không có dữ liệu, biểu đồ có bị gãy không?
3. Với phần PowerQuery em làm, có cách nào tối ưu hơn không?
Cảm ơn anh.
Câu 1

let
    Source = Table.NestedJoin(MH,{"Sản phẩm"},DM,{"Sản phẩm"},"DM",JoinKind.LeftOuter),
    #"Expanded DM" = Table.ExpandTableColumn(Source, "DM", {"Định mức"}, {"DM.Định mức"}),
    #"Added Custom" = Table.AddColumn(#"Expanded DM", "Mua", each [Số lượng]*[Đơn giá]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ĐM", each [Số lượng]*[DM.Định mức]),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Nhân viên", "Sản phẩm"}, {{"Tổng Mua", each List.Sum([Mua]), type number}, {"Tổng ĐM", each List.Sum([ĐM]), type number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Ghi chú", each if [Tổng Mua] = [Tổng ĐM] then "Bằng" else if [Tổng Mua] < [Tổng ĐM] then "Thấp hơn" else "Cao hơn")
in
    #"Added Conditional Column"

Câu 2

let
    Source = Table.NestedJoin(MH,{"Sản phẩm"},DM,{"Sản phẩm"},"DM",JoinKind.LeftOuter),
    #"Expanded DM" = Table.ExpandTableColumn(Source, "DM", {"Định mức"}, {"DM.Định mức"}),
    #"Added Custom" = Table.AddColumn(#"Expanded DM", "ĐM", each [Số lượng]*[DM.Định mức]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "MuaCK", each if [Số lượng]>3 then (3*[Đơn giá])+([Số lượng]-3)*[Đơn giá]*0.95 else [Số lượng]*[Đơn giá]),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Nhân viên", "Sản phẩm"}, {{"Tổng Mua sau CK", each List.Sum([MuaCK]), type number}, {"Tổng ĐM", each List.Sum([ĐM]), type number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Ghi chú", each if [Tổng Mua sau CK] = [Tổng ĐM] then "Bằng" else if [Tổng Mua sau CK] < [Tổng ĐM] then "Thấp hơn" else "Cao hơn")
in
    #"Added Conditional Column"

Câu 3

let
    Source = Table.NestedJoin(MH,{"Sản phẩm"},DM,{"Sản phẩm"},"DM",JoinKind.LeftOuter),
    #"Expanded DM" = Table.ExpandTableColumn(Source, "DM", {"Định mức"}, {"DM.Định mức"}),
    #"Grouped Rows" = Table.Group(#"Expanded DM", {"Tỉnh", "Sản phẩm"}, {{"ĐG TB", each List.Average([Đơn giá]), type number}, {"MĐ", each List.Average([DM.Định mức]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Percent", each ([ĐG TB]/[MĐ])-1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Percent", Percentage.Type}})
in
    #"Changed Type1"

 

[tranhungdao12a3]

Cảm ơn anh @tranhungdao12a3
Em gửi bổ sung cách tính bằng PowerQuery, bài này em hoàn toàn dùng các bước của PowerQuery thôi, chưa đạt đến trình độ gõ trực tiếp vào Advanced Editer được.
Theo bài giải của anh thì phần Tổng tiền mua sau Chiết khấu anh đang hiểu nhầm 1 chút là cứ mua đơn hàng số lượng > 3 là đơn giá 95%. Tuy nhiên đề bài là chỉ Chiết khấu cho các Sản phẩm có số lượng mua từ 4 trở lên.
Em có 3 nội dung muốn hỏi thêm anh.
1. Trong DAX có cách nào lọc trước để những dòng không có kết quả thì không cho hiện ra được không?
Ví dụ bảng kết quả số 3, nhiều SP không mua nên không có đơn giá Trung bình theo tỉnh, nhưng vẫn mang đi so sánh với đơn giá định mức
2. Với bảng dữ liệu này, nếu đưa vào Pivot Chart thì các dòng không có dữ liệu, biểu đồ có bị gãy không?
3. Với phần PowerQuery em làm, có cách nào tối ưu hơn không?
Cảm ơn anh.
Câu 1

let
    Source = Table.NestedJoin(MH,{"Sản phẩm"},DM,{"Sản phẩm"},"DM",JoinKind.LeftOuter),
    #"Expanded DM" = Table.ExpandTableColumn(Source, "DM", {"Định mức"}, {"DM.Định mức"}),
    #"Added Custom" = Table.AddColumn(#"Expanded DM", "Mua", each [Số lượng]*[Đơn giá]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ĐM", each [Số lượng]*[DM.Định mức]),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Nhân viên", "Sản phẩm"}, {{"Tổng Mua", each List.Sum([Mua]), type number}, {"Tổng ĐM", each List.Sum([ĐM]), type number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Ghi chú", each if [Tổng Mua] = [Tổng ĐM] then "Bằng" else if [Tổng Mua] < [Tổng ĐM] then "Thấp hơn" else "Cao hơn")
in
    #"Added Conditional Column"

Câu 2

let
    Source = Table.NestedJoin(MH,{"Sản phẩm"},DM,{"Sản phẩm"},"DM",JoinKind.LeftOuter),
    #"Expanded DM" = Table.ExpandTableColumn(Source, "DM", {"Định mức"}, {"DM.Định mức"}),
    #"Added Custom" = Table.AddColumn(#"Expanded DM", "ĐM", each [Số lượng]*[DM.Định mức]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "MuaCK", each if [Số lượng]>3 then (3*[Đơn giá])+([Số lượng]-3)*[Đơn giá]*0.95 else [Số lượng]*[Đơn giá]),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Nhân viên", "Sản phẩm"}, {{"Tổng Mua sau CK", each List.Sum([MuaCK]), type number}, {"Tổng ĐM", each List.Sum([ĐM]), type number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Ghi chú", each if [Tổng Mua sau CK] = [Tổng ĐM] then "Bằng" else if [Tổng Mua sau CK] < [Tổng ĐM] then "Thấp hơn" else "Cao hơn")
in
    #"Added Conditional Column"

Câu 3

let
    Source = Table.NestedJoin(MH,{"Sản phẩm"},DM,{"Sản phẩm"},"DM",JoinKind.LeftOuter),
    #"Expanded DM" = Table.ExpandTableColumn(Source, "DM", {"Định mức"}, {"DM.Định mức"}),
    #"Grouped Rows" = Table.Group(#"Expanded DM", {"Tỉnh", "Sản phẩm"}, {{"ĐG TB", each List.Average([Đơn giá]), type number}, {"MĐ", each List.Average([DM.Định mức]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Percent", each ([ĐG TB]/[MĐ])-1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Percent", Percentage.Type}})
in
    #"Changed Type1"

Bạn muốn bỏ dòng trống khi không có dữ liệu mua thì bạn có thể Siler bỏ tick chọn Blank đi, hoặc thêm hàm If trong hàm percent
Về câu hỏi Power query của bạn, tôi không nói là power query không làm được nhưng bạn cần hiểu chức năng nhiệm vụ của hai thứ này.
Power query điểm mạnh của nó là ETL(Extract - Transform - Load) Trong khi đó phân tích là nhiệm vụ của Dax power pivot hay power bi, vì Dax có tính chất kế thừa và mở rộng, dễ dàng chỉnh sửa.Bạn có thể tìm một bài tổng quan mà anh VetMini có nói trong chủ đề nào đó(tìm kiếm sẽ thấy). Trong thực tế công việc phân tích không chỉ có vài yêu cầu đơn giản như vậy, mà còn nhiều yêu cầu phức tạp hơn, không thể nào với mỗi câu hỏi bạn lại làm rất nhiều bước như Power query được. Dax hỗ trợ kéo thả thông qua Relationship giữa các bảng với nhau. Cho nên những phân tích thì nên là công việc của Dax nhé! Tôi chỉ muốn nói để bạn hiểu là với công việc nào thì dùng hướng đi gì chứ tôi không nói Power query không làm được nhé! Bạn nói hai cái thì tôi so sánh 2 cái, còn giải nó thì thiếu gì cách: hàm excel, VBA, sql, ngôn ngữ lập trình khác,...nhất thiết gì Power query, power pivot.

 

[Quangdz0512]

Bạn muốn bỏ dòng trống khi không có dữ liệu mua thì bạn có thể Siler bỏ tick chọn Blank đi, hoặc thêm hàm If trong hàm percent
Về câu hỏi Power query của bạn, tôi không nói là power query không làm được nhưng bạn cần hiểu chức năng nhiệm vụ của hai thứ này.
Power query điểm mạnh của nó là ETL(Extract - Transform - Load) Trong khi đó phân tích là nhiệm vụ của Dax power pivot hay power bi, vì Dax có tính chất kế thừa và mở rộng, dễ dàng chỉnh sửa.Bạn có thể tìm một bài tổng quan mà anh VetMini có nói trong chủ đề nào đó(tìm kiếm sẽ thấy). Trong thực tế công việc phân tích không chỉ có vài yêu cầu đơn giản như vậy, mà còn nhiều yêu cầu phức tạp hơn, không thể nào với mỗi câu hỏi bạn lại làm rất nhiều bước như Power query được. Dax hỗ trợ kéo thả thông qua Relationship giữa các bảng với nhau. Cho nên những phân tích thì nên là công việc của Dax nhé! Tôi chỉ muốn nói để bạn hiểu là với công việc nào thì dùng hướng đi gì chứ tôi không nói Power query không làm được nhé! Bạn nói hai cái thì tôi so sánh 2 cái, còn giải nó thì thiếu gì cách: hàm excel, VBA, sql, ngôn ngữ lập trình khác,...nhất thiết gì Power query, power pivot.

Cảm ơn anh đã chỉ em đường đi nước bước. Do em đang học về 2 cái này nên muốn giải bằng 2 cách. Còn đúng là bài này còn nhiều phương pháp khác để giải.
PowerQuery và PowerPivot có mối quan hệ mật thiết với nhau, như vậy thì em sẽ ưu tiên dùng PowerQuery để chuẩn hoá dữ liệu và đẩy vào Data Model, còn phân tích sẽ tập trung vào DAX và trả kết quả qua Power Pivot.