Rankx

[Anonymous]

sales group	year	volume
abc	2022	56
abc	2023	65
abc	2024	112
xyz	2022	43
xyz	2023	34
xyz	2024	55
mno	2022	76

I have a data which looks like this.
I want to use sales group as legend in one of the visual.
the data has more than 30 sales group. Hence i want to make a measure which will give me top 12 sales group to be used as legend depending upon there volume of next three years.

Example:
from above dataset
abc = 56+65+112 = 233
xyz = 132

hence abc should be ranked as 1 and xyz to be second dynamically and the measure can be used as Legend in bar chart.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

I create a new table with DAX as below to help calculate the ranking of groups. Then use the "Is Top 5" column as a filter on the column chart. 

Table 2 = SUMMARIZE('Table','Table'[Sales Group],"Sum of Volume",SUM('Table'[Volume]))

Rank = RANKX('Table 2','Table 2'[Sum of Volume],,DESC,Dense)

Is Top 5 = IF('Table 2'[Rank] <= 5, 1)

The sample file has been attached at bottom. Hope it helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.