Results 1 to 3 of 3

Thread: DAX formula - Cumulative Count

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Location
    Pittsburgh
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default DAX formula - Cumulative Count

    Hello,

    I am trying to do something like this: I have a column with names of cities:

    Richmond
    Cleveland
    Cleveland
    Boston
    Cleveland
    Boston

    I would like a cumulative count down the column in PowerPivot:

    Richmond 1
    Cleveland 1
    Cleveland 2
    Boston 1
    Cleveland 3
    Boston 2

    I have tried the following, but it just returns the total count to each matching row:

    Code:
    =CALCULATE (
        COUNTROWS ( CityList ),
        FILTER (
            CityList,
            CityList[CityName]
                = EARLIER ( CityList[CityName]
     )
        )
    )
    Thanks in advanced!

  2. #2
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DAX formula - Cumulative Count

    First: this will work only in calculated column
    Second: it requires index column

    Code:
    =CALCULATE (
        COUNTROWS ( CityList ),
        FILTER (
            CityList,
            CityList[CityName]
                = EARLIER ( CityList[CityName]) && CityList[Index] <= EARLIER(CityList[Index])
     
        )
    )

  3. #3
    Board Regular
    Join Date
    Jun 2009
    Location
    Pittsburgh
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DAX formula - Cumulative Count

    That did it, many thanks. Don't have a great understanding of the EARLIER formula, and definitely had no idea you could even do a 3 item equality check like that.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •