Top 5 Items in Table

Question

Given the following data:

I would like to get the top 5 results from the table. I tried the following formulas:

Measure: Violation Ranking = RANKX(ALL(Violation_Data), COUNTA(Violation_Data[Violation]))

Column: Top Violations = IF([Violation Ranking] > 5, "Others", Violation_Data[Violation])

But I'm still getting all of the results from [Violation] (I think it has something to do with the Calculated Column). I tried using a Visual Filter instead (show only [Violation Ranking] <= 5), but I'm still getting the same results (the [Violation Ranking] of each [Violation] is 1 (which is odd, to say the least) that's why all of the violations are displayed.

Anyone know how to correct the given DAX code?

Answer 1

You can do this:

In the 'Modeling' tab, click 'New Table'...

Then use TOPN and GROUPBY, like this:

Violation_Counts = TOPN(5,GROUPBY(Violation_Data,Violation_Data[Violation],"Count",countx(CURRENTGROUP(),Violation_Data[Name])))

...which will give you a new table of the Top 5 Violations, like this:

Answer 2

Okay, so I think I got it. I was able to use Visual Filters to select just the top 5 most recurrent Violations in the table like so: