I've tried asking this question before about a different project, but I still have not figured it out.
In this project I am once again trying to create an Other category in a pie chart with a ranked variable, but this time I am trying to rank a string field (LocationOfLossState, or State) by the Count of a fixed decimal field (ClaimNumber).
See this pie chart. I want a graph that has slices for Wisconsin and Utah (the top two States) and an Other column for all the other States in the chart.
So far I've created a Rank column and a Category column as shown in this post (and below with my variable names). It seems the Category column is doing what it should, but the Rank column is not.
Rank = RANKX('DimClaim','DimClaim'[LocationOfLossState])
The Rank column (which is hidden) should have marked Wisconsin #1 (with the most entries) and Utah #2 (with the second most), and all the other states somewhere lower. Instead, it marked Wyoming #1 (with four entries), Wisconsin #5 (with 20,467 entries), West Virginia #20,472 (with two entries), etc.
I figured out what it actually did while I was writing that last paragraph. It put the states in reverse alphabetical order and then gave each state a rank that was the number of its first entry in that order. Kind of like this:
Wyoming 1
Wyoming 1
Wyoming 1
Wisconsin 4
Wisconsin 4
West Virginia 6
Washington 7
Washington 7
Washington 7
Virginia 10
And here's what the chart looks like.
As for the Category column, it seems to be doing its job of returning the State name if the Rank column is 2 or less and returning "Other" if not.
Category = IF('DimClaim'[Rank]<=2,'DimClaim'[LocationOfLossState],"Other")
Again, what I need to fix is the Rank column. The Rank column (which is hidden) should rank the states from 1 (state with the most claims) to 41 (state with the fewest claims). (9 states have no claims here.) And the key to the chart (the Category column) should show the state names for #1 and #2, but hide them for the rest of the states (which it is doing).
I've already looked over this article that explains how the rankx function works. I've tested it out with the Dense option for ties, and the key did in fact show Wyoming and Wisconsin as separate categories from the others, but I want it to sort by Count instead.
Here's what that graph looks like.
Adding this as an answer as suggested. "Grouping and Binning" is what you are looking for. Grouping and Binning
