How to rank a clustered bar chart by only one aspect of the legend

[randomrandom]

Hi all,

I'm hoping someone can help me as I've scanned arround other queries and not found an adequate solution.

I have the below chart which shows the top 20 items by total revenue, split by year. It has selected the top 20 based on the sum of the total revenue of both 2020 and 2021.  This result is unsurprising, but not exactly what I'd like.

Ideally I would like it to show the top 20 items by only the 2021 revenue, but still include the 2020 revenue for comparison. So my user can see what sold well last year, compared with how it sold the year before. I'm confident my users priority is what sold well most recently (2021), and then seeing a year-on-year change is a bonus.

As an example, if this visual was how I'd like it, the second item from the top would appear much lower down the list because the 2021 revenue is so low, and the item 7th from the bottom would appear higher up, despite having basically 0 in 2020.

I have tried creating an additional column in my dataset that only contains 2021 revenue (and just has 0 for all rows where the year is 2020). I used this new column in the "By value" section of the description filter. This also failed spectacularly:

Am I chasing something that doesn't exist? Am I being a dumdum? Plz help.

Thanks.

[v-rongtiep-msft · Accepted solution]

Hi @randomrandom ,

I have create simple sample. Please refer to it to see if it helps you.

Have a try.

Create a table.

Table 2 =
FILTER ( ALL ( 'Table' ), 'Table'[invol_year] = 2021 )

Then rankx the data.

Column = RANKX('Table 2','Table 2'[sales],,DESC)

Then create the relationship between them.

If I have misunderstood your meaning, please provide a pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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