Distinct count and then create groups

[JoyceW]

Hi,

I'm looking for some help. 

I have a table with ordernumbers and invoicenumbers. Ordernumber is unique, but one order can have more invoicenumbers. 

So for example:

Invoicenumber	Ordernumber
1	1
2	1
3	2
4	3
5	3
6	3
7	4
8	4

I want to group by how many invoices there are for an order. 

So first I created a measure: count of invoicenumber = calculate(distinctcount(table[invoicenumber]))

So now I have:

Ordernumber	Count of invoices
1	2
2	1
3	3
4	2

Then I want to group by count of invoices, so I know how many times I have 1, 2, 3 etc. I don't know how to get there, but I would like the result to be like this:

Group of count of invoices	Amount
1	1
2-3	3
4 - more	0

So then I will know that there are 3 orders which have 2 or 3 invoices, 0 orders with 4 or more invoices, e.g

[JoyceW]

Yes! Thank you, this works like charm. 

[Jihwan_Kim · Accepted solution]

Hi,

One of ways to create this is having a Grouping table like below.

Please check the below picture and the attached pbix file.

Invioce count group measure: = 
COUNTROWS (
    FILTER (
        VALUES ( Data[Ordernumber] ),
        [Count of invoice measure:] >= MIN ( 'Grouping'[min] )
            && [Count of invoice measure:] <= MAX ( 'Grouping'[max] )
    )
) + 0