Creating a Column based on a Ranking Measure

[Gladiator909]

Hello Everyone!

I am trying to create a column based on the ranking of values.

Let me explain a bit more.

I have the following table:

Color	Count
Black	25
Blue	20
Green	15
Brown	14
Purple	12
Red	10
Cyan	9

I created a Ranking Measure that ranks the counts in descending order as follows:

TOTAL = sum('Table'[Count])

Ranking = RANKX(all('Table'[Color]),[TOTAL],,DESC,Dense)

And here is my resulting visual table:

If I need to represent the data as a pie chart, this is what I get:

What I would like to see, is to have a Pie Chart with the top 3 colors and the rest of the colors labelled as "Other".

Since we cannot use a measure as a legend, I am guessing Im gonna have to create a column based on the ranking measure I created before and use that as my legend? Is that correct?

This is what I did:

categorization = SWITCH([Ranking],1,'Table'[Color],2,'Table'[Color],3,'Table'[Color],"Other")

Unfortunately, this does not work and I get the same graph back.

And since that table is constantly updated, colors can become top 3 and colors can drop off the top 3 as well.

Any ideas how to make it work?

 thank you

[v-tangjie-msft · Accepted solution]

Hi @Gladiator909 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

others total = SUMX(FILTER(ALL('Table'), not [Ranking] in {1,2,3}),[TOTAL])

(3) We can create a table. 

Table 2 = 
var _a = SELECTCOLUMNS(FILTER('Table',[Ranking]  in {1,2,3}),"color",[Color],"total",[TOTAL])
var _b=ADDCOLUMNS(DATATABLE ( 
    "Color", STRING,
    {
        { "Others" }
    }
)
,"total",[others total])
return UNION(_a,_b)

(4) Then the result is as follows.

Best Regards,

Neeko Tang

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

[vanessafvg]

can you share what you got back, and also can you share your data in text format?

[Gladiator909]

@vanessafvg Thanks for the reply.

The pie chart looks like this:

I think its because the underlying table looks like this now:

its not letting me attach the table as a text file. Any idea how I can do that?

[vanessafvg]

just copy and past the text in, that should be good enough.

[Gladiator909]

Thank you for your help.

Color Count
red 10
blue 20
green 15
yellow 9
white 5
black 25
purple 12
orange 6
cyan 9
brown 14

[rsbin]

@Gladiator909 ,

You are on the right track with SWITCH, just think the syntax is incorrect:

New Category = SWITCH(
                  TRUE(),
                  [Ranking]<=3,'Table'[Color],
                  "Other" )

Then use this new column in your pie chart visual.

If this does not resolve your issue, google something like "power bi group others" and pretty sure you will find a solution there.

Regards,