Forum Discussion

rhys1987's avatar
rhys1987
Frequent Visitor
5 years ago
Solved

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.   P...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

     

AlB's avatar
AlB
Community Champion
5 years ago

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