Count number of occurrences per month and rank them in unique manner

[seba]

I have the below table. The task is to count number of occurrences of each category per each month and then present top ten (count wise) categories on the 100% stacked column chart. I am able to count occurrences , but then power bi sums them for selected period of time (multiple months for example). I tried to rank them, but if number of occurrences is same, then the same rank is assigned and this leads to incorrect data as numbers are added again. Your help is greatly appreaciated. Using powerBi desktop with report server.

Item	Created Month	Category	Assigne
Item A	Jan-20	X	F
Item B	Feb-20	Y	E
Item C	Jan-20	Z	D
Item A	Apr-20	X	D
Item B	Jan-20	Y	D
Item C	Feb-20	Z	E
Item A	Mar-20	Z	F
Item B	Jan-20	Z	F
Item C	Mar-20	Y	F

[sturlaws · Accepted solution]

Hi @seba 

can you give this a go:

number of occurences =
CALCULATE (
    COUNT ( 'Table'[Item] ),
    FILTER (
        VALUES ( 'Table'[Category] ),
        RANKX (
            ALL ( 'Table'[Category] ),
            CALCULATE ( COUNT ( 'Table'[Item] ) )
                + INT ( CALCULATE ( UNICODE ( MIN ( 'Table'[Category] ) ) ) ) / 100000,
            ,
            DESC
        ) <= 10
    )
)

The 

+ INT ( CALCULATE ( UNICODE ( MIN ( 'Table'[Category] ) ) ) ) / 100000

-part is for breaking any ties

Cheers,
Sturla

[seba]

Wow, that is amazing and impressive! It does work! Thank you very much!