Get list of values from the second most occurrences

[Adammhm]

Hi there,

I have 2 fields - Incident Type and Department: I am trying to display a table that lists the incident type for the second most occur department. 

Incident Type	Department
A	X
B	X
C	X
C	X
D	Y
D	Y
E	Y

I would like to get a table that shows:

Incident Type	Count of Incident type
D	2
E	1

Is this achievable? Thanks in advance

[v-angzheng-msft · Accepted solution]

Hi, @Adammhm 

Try to create a measure like this:

Rank = 
RANKX(ALL('Table'),CALCULATE(COUNT('Table'[Incident Type]),ALLEXCEPT('Table','Table'[Department])),,DESC,Dense)

And to show items when measure is 2.
Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Adammhm , Create measure like these

cnt = count(table[Department])

rank = rankx(allselected(Table[Incident Type]), [cnt],,desc,dense)

Rank2 = sumx(filter(values(Table[Incident Type]) , [Rank] =2), [cnt])

[Adammhm]

Does this work with date slicer?

[Adammhm]

Hi @amitchandak ,

Thank you for the prompt response. I have created the measures as you suggested and have Incident type and Count of Incident Type in the Values, and [Rank2] placed in the filters on this visual. The table didn't filter out other incident types. Sorry if I have done it incorrectly, I'm new to Power BI.