Count different rows per debtor

[Anonymous]

As you can see I have the column debtor, total months ordered, group months ordered and total of group. I need to know the formula for the last column. I allready tried other formula's but it didn't work out yet. As you can see debtor 1008 ordered in 3 months total, 1009 ordered in 6 of the 12 months in total etc. So i ordered it by Groups, if the debtor ordered in 3 monts it will go to 1 till 3 months and if the debtor orderd in 6 months total, it will go to group 4 till 6 months etc. As you can see there are in total 4 debtors who ordered in group 1 till 3 months and 3 debtors ordered in 4 till 6 months. I need to know the formula that you can see how many debtors ordered in a specific group. I allready tried something but it didn't work out as expected. It counted the total times 1 till 3 months and not the total times a debtor orderd in 1 till 3 months etc. There is a chance that a debtor appears more than once in a table, because he can also place more orders.

Suppose there are 300 debtors who order in 1 till 3 months, then there must be 300 as answer. Those 300 debtors placed, for example 1500 orders in total, so there will be 1500 rows. Then there will also be several lines and that line will also display 1 till 3 monts. So it happens now that the answer on that line is 1500, because there are 1500 rows where 1 till 3 months is. But only 300 debtors ordered, so the answer I would like to see on that line would be 300. That debtor only has to be counted once instead of every line, that is what happens with this formula now. 

Could you help my out?

Debtor    Total months ordered      Group months ordered     Total of group
1008        3                                       1 till 3 months                        4
1009       6                                        4 till 6 months                       3
1014       11                                     10 till 12 months                    2
1032       4                                       4 till 6 months                         3
1054       8                                       7 till 9 months                         2
1078       1                                       1 till 3 months                         4
1096       9                                       7 till 9 months                         2
1121       3                                       1 till 3 months                         4
1153       5                                       4 till 6 months                         3
1174       10                                    10 till 12 months                      2
1204       2                                      1 till 3 months                         4

Kind regards,

Rick

[Ashish_Mathur]

Hi,

Try this calculated column formula

=CALCULATE(DISTINCTCOUNT(Data[Debtor]),FILTER(Data,Data[Group Months Ordered]=EARLIER(Data[Group Months Ordered])))

Hope this helps.

[Anonymous · Accepted solution]

Rick,

see if this calculated column is what you had in mind:

New Calculated Column = 
var __CurrentGroup = Table2[Grouped Months Ordered]
RETURN

CALCULATE(
    DISTINCTCOUNT(Table2[Debtor]),
    FILTER( 
        Table2,
        Table2[Grouped Months Ordered] = __CurrentGroup
    )
)