Running count with multiple conditions

[rhys1987]

Hi,

Below is a simple example of the calculated column I am trying to create.

Last row showing the running count based on product and customer being the same. Order Qty is not relevant.

Product	Customer	Order Qty	Count
A	123	5	1
A	123	5	2
A	123	5	3
B	456	5	1
C	456	5	1
C	456	5	2

Thanks,

[AlB · Accepted solution]

Hi @rhys1987 

You need to first add an index column to the table to indicate precedence. Otherwise there is no way to differentiate those rows in your count. You can do that easily in PQ. Then your calculated column:

Col =
CALCULATE (
    COUNT ( Table1[Product] ),
    ALLEXCEPT ( Table1, Table1[Product], Table1[Customer] ),
    Table1[Index] <= EARLIER ( Table1[Index] )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers