Forum Discussion

francomaestri's avatar
francomaestri
Frequent Visitor
3 years ago
Solved

Cumulative count by categories

Greetings,

 

I'm trying to count the occurrence of certain events on some dates, but I can't calculate it correctly, so when I create a chart, the occurrence of a new event (new category) starts from zero. That is, each line on the chart starts from zero the first time it appears.

 

This is what I have so far: 

(Table as an example)

DateCategory
08-07-2022A
08-07-2022B
08-07-2022A
14-08-2022A
14-08-2022C
09-09-2022C
09-09-2022A
09-09-2022B
09-09-2022B

 

COUNTING = CALCULATE( COUNTROWS('Table'),FILTER(ALL('Table'), 'Table'[Category] <= MAX('Table'[Category]) && 'Table'[Date] <= MAX('Table'[Date])))

 

Need Help
  • francomaestri's avatar
    francomaestri
    3 years ago

    Ok, I solved it.

    I just created an index in power query, then I created a new column with rankx:

     

    Count with rankx= RANKX(
        FILTER ( 'Table', 'Table'[Category] = EARLIER ( 'Table'[Category] ) ),
        'Table'[Index], ,ASC )

     

    Then I just put Date in X-axis, the new column in Y-axis and Category in legend.
    This way I can see when a new event occurs and how many times is repeated over time an when.

     

7 Replies

  • Jayee's avatar
    Jayee
    Responsive Resident
    3 years ago

    Hi francomaestri 

     

    Counting =

    VAR MaxDate = MAX ( 'Table'[Date] ) 

    RETURN

        CALCULATE (

            COUNTROWS('Table'),

            'Table'[Date] <= MaxDate,

            ALL ( Table )

        )

     

    Use this measure in chart and add Category as legend.

     

    If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!

     

    • francomaestri's avatar
      francomaestri
      Frequent Visitor
      3 years ago

      Sadly I'm having the same problem. Each new event starts from a higher value, not from zero.

       

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago
    If there is no misunderstanding, you measure shall be like this:
    COUNTING = 
    VAR CurrentDate = MAX('Table'[Date])
    RETURN
    CALCULATE( 
        COUNTROWS('Table'),
        FILTER(
            ALL('Table'), 
            'Table'[Date] <= CurrrentDate
        )
    )
     
    The [Category] field shall be put in the Legend of the linechart visual, or?
    • francomaestri's avatar
      francomaestri
      Frequent Visitor
      3 years ago

      I'm having the same problem. When a new event appears it does not start from zero.

       

    • francomaestri's avatar
      francomaestri
      Frequent Visitor
      3 years ago

      Hi!
      I think that the result in a table would be:

      DateCategoryCount
      08-07-2022A1
      08-07-2022B1
      08-07-2022A2
      14-08-2022A3
      14-08-2022C1
      09-09-2022C2
      09-09-2022A4
      09-09-2022B2
      09-09-2022B3

       

      If I'm not mistaken, in this way in the graph, for example, the line of category C will start at 1 on 08-14-2022. I could just split each event type (category) into a different column, but the category of events may change in the future, so I wanted to keep the table as it is (also because there are many more columns than shown here).

       

  • francomaestri's avatar
    francomaestri
    Frequent Visitor
    3 years ago

    Ok, I solved it.

    I just created an index in power query, then I created a new column with rankx:

     

    Count with rankx= RANKX(
        FILTER ( 'Table', 'Table'[Category] = EARLIER ( 'Table'[Category] ) ),
        'Table'[Index], ,ASC )

     

    Then I just put Date in X-axis, the new column in Y-axis and Category in legend.
    This way I can see when a new event occurs and how many times is repeated over time an when.