Forum Discussion
Rankx
| 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.
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.
1 Reply
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.