Forum Discussion

dgdgdg122db's avatar
dgdgdg122db
Helper II
6 years ago
Solved

Count occurrence with condition

Hi  I have a table looks like this: What I want to achieve is to count the occurrence of the client no. where the Position is 10.   I want to use Card visualization in my report:  1. have ...
  • jdbuchanan71's avatar
    6 years ago

    Hello dgdgdg122db 

    We need just a few measures to get what you are looking for.

    A count of all the rows, this we will use for filtering later.

    Line Count = COUNTROWS ( 'Table' )

    A count of the distinct customers (the 20)

    Buying Customers = DISTINCTCOUNT ( 'Table'[Client no] )

    The first time buyers

    First Time Buyers = 
    CALCULATE ( 
        [Buying Customers], FILTER ( VALUES ( 'Table'[Client no] ) , [Line Count] = 1 )
    )

    And the returning customers

    Returning Customers = 
    CALCULATE ( 
        [Buying Customers], FILTER ( VALUES ( 'Table'[Client no] ) , [Line Count] > 1 )
    )

    I'm not sure what you mean about the filter on position 10 but the position filed can be added as a page level filter and that will limit all the measures above to only calculate against the lines in position 10 if that is what you are looking for.

    I have attached my sample file for you to look at.