Forum Discussion

AnthonyJoseph's avatar
AnthonyJoseph
Icon for Resolver III rankResolver III
4 years ago
Solved

show top 5 based on category

Hello Community,   I have been assigned a task to filter the top 5 category items based on a sum of values. I have two tables (Table Year and Table detail) as mentioned below.   I need to show a ...
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    Hi:

    The chart you want is designed to follow the order of the first year, then from there I'm not sure it appropriates space. Notice position 1 & 2 - bycycle then bike. then Heavy Bikes. It's a bad chart to use(my opinion) as the other options more clearly show what is going on between the categories and years. 2020 is definitely lower than 2021 but it's hard to tell with the chart you select. The two charts to the right appear to show both individual category and grand total differences. I wish I knew why the spacing is a bit off.

     

     

tackytechtom's avatar
tackytechtom
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Hi AnthonyJoseph ,

 

I have a solution here:

 

 

Here the DAX:

Top5Measure = 
VAR _rank =
    RANKX(
        ALLSELECTED ( Table ),
        CALCULATE ( 
            [ValueSumMeasure], 
            MAX (Table[emplate no] ) = Table[emplate no]
        ), 
        , DESC
        , DENSE 
    )

VAR Top5 =
    IF ( _rank <= 5, _rank, BLANK () )
RETURN
    Top5

 

Note, the ValueSumMeasure is simply the SUM over ther Value column.

 

Let me know if this solves your issue 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

AnthonyJoseph's avatar
AnthonyJoseph
Icon for Resolver III rankResolver III
4 years ago

Thanks tackytechtom The DAX gives me the rank of the top 5 items however, when I plot it to a graph stacked bar chart I get blanks in between the bars for the items that are not in a year. Example Bike is  in top 5 of the 2019 but when plotted to graph year 2020 has blank value in bar. Similarly Car is showing has blank in 2019 since we dont have car in the 2019 in top 5 values but its there in top 5 as 2020.