Forum Discussion
Running count with multiple conditions
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,
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
1 Reply
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