Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello community
I had problem with calculating how many customers did order full 3 sections, how many two times, how many three times and so on?
For example: The result I expected like: 2 customer have order 3 sections and one times.
I dont know how to calculate it. please help me solve this case.
Solved! Go to Solution.
Hi @TrangHoThu ,
One way to do this is by double-grouping table.
First, group by [Customer] and [Sale Document] and add a Distinct Count aggregation so we know how many sections were ordered in each customer transaction.
Then, group by [Customer] and your first Distinct Count column, adding another Distinct Count column so we know how many times each customer ordered each number of sections.
This example query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8snMqVTSUTIEYkelWB0UASd0AWewgGNeXiKQYwTXgiQA0eKaC9FjDFeCLIKpxhnZIhN0p5ig22wKNwRJAMUMM3QzzNC9YwbREgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, SaleDocument = _t, Section = _t]),
groupCustomerDocument = Table.Group(Source, {"Customer", "SaleDocument"}, {{"NumberSectionsOrdered", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
groupCustomerSectionOrdered = Table.Group(groupCustomerDocument, {"Customer", "NumberSectionsOrdered"}, {{"NumberTimesSectionsOrdered", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
groupCustomerSectionOrdered
...turns this:
...into this:
...so we can see:
Anna has ordered 2 sections twice
Emily has ordered 3 sections once
Lily has ordered 2 sections once, and 3 sections twice
Pete
Proud to be a Datanaut!
That worked! Thanks a lot
Hi @TrangHoThu ,
One way to do this is by double-grouping table.
First, group by [Customer] and [Sale Document] and add a Distinct Count aggregation so we know how many sections were ordered in each customer transaction.
Then, group by [Customer] and your first Distinct Count column, adding another Distinct Count column so we know how many times each customer ordered each number of sections.
This example query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8snMqVTSUTIEYkelWB0UASd0AWewgGNeXiKQYwTXgiQA0eKaC9FjDFeCLIKpxhnZIhN0p5ig22wKNwRJAMUMM3QzzNC9YwbREgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, SaleDocument = _t, Section = _t]),
groupCustomerDocument = Table.Group(Source, {"Customer", "SaleDocument"}, {{"NumberSectionsOrdered", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
groupCustomerSectionOrdered = Table.Group(groupCustomerDocument, {"Customer", "NumberSectionsOrdered"}, {{"NumberTimesSectionsOrdered", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
groupCustomerSectionOrdered
...turns this:
...into this:
...so we can see:
Anna has ordered 2 sections twice
Emily has ordered 3 sections once
Lily has ordered 2 sections once, and 3 sections twice
Pete
Proud to be a Datanaut!