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 fr...
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.

     

mangaus1111's avatar
mangaus1111
Solution Sage
3 years ago

Hi francomaestri ,

if it were a table is this your expected result? If not, please show what is your expected result.

 

  • 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).