How many customers did order full 3 sections, how many two times, how many three times and so on?

[TrangHoThu]

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.

[TrangHoThu]

That worked! Thanks a lot

[BA_Pete · Accepted 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