Forum Discussion

JoyceW's avatar
JoyceW
Helper II
4 years ago
Solved

Distinct count and then create groups

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:

 

InvoicenumberOrdernumber
11
21
32
43
53
63
74
84

 

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:

 

OrdernumberCount of invoices
12
21
33
42

 

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 invoicesAmount
11
2-33
4 - more0

 

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

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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