Forum Discussion
Resolver IIIshow 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 column bar chart that display top 5 categories (irrespective of the attribute) for each year. Is this something that's possible in DAX?
I tried implementing it but ended by with blanks between bars in the visual. Is there anyway we can not include those blank bars in visuals
Table - Detail
| Template no | Attribute | Category | Value |
| 1 | Vehicle | Bike | 100 |
| 1 | Vehicle | Scooter | 200 |
| 1 | Vehicle | Heavy vehicle | 140 |
| 1 | Vehicle | bicycle | 240 |
| 1 | Vehicle | car | 50 |
| 1 | Non-vechile | Rocket | 20 |
| 1 | Non-vechile | Helicopter | 30 |
| 1 | Non-vechile | Airplane | 45 |
| 1 | Non-vechile | Aircraft | 56 |
| 1 | Non-vechile | Jet | 70 |
| 2 | Vehicle | Bike | 10 |
| 2 | Vehicle | Scooter | 20 |
| 2 | Vehicle | Heavy vehicle | 30 |
| 2 | Vehicle | bicycle | 10 |
| 2 | Vehicle | car | 50 |
| 2 | Non-vechile | Rocket | 20 |
| 2 | Non-vechile | Helicopter | 67 |
| 2 | Non-vechile | Airplane | 87 |
| 2 | Non-vechile | Aircraft | 23 |
| 2 | Non-vechile | Jet | 49 |
| 3 | Vehicle | Bike | 46 |
| 3 | Vehicle | Scooter | 23 |
| 3 | Vehicle | Heavy vehicle | 45 |
| 3 | Vehicle | bicycle | 67 |
| 3 | Vehicle | car | 12 |
| 3 | Non-vechile | Rocket | 98 |
| 3 | Non-vechile | Helicopter | 76 |
| 3 | Non-vechile | Airplane | 34 |
| 3 | Non-vechile | Aircraft | 22 |
| 3 | Non-vechile | Jet | 18 |
Table Year:
| Template no | Year |
| 1 | 2019 |
| 2 | 2020 |
| 3 | 2021 |
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.
14 Replies
- Fowmy
Super User
AnthonyJoseph
If you tried the small multiples option in your charts, it won't allow you to hide the categories with blank values, The other option is to show it in a matrix as follows. You can also search for a custom visual if one exists.Top 5 Categories = CALCULATE( [Sum of Value], KEEPFILTERS( TOPN( 5 , ALLSELECTED( Table1[Category] ) , [Sum of Value] , DESC ) ) )
The file is attached below my signature - tackytechtom
Most Valuable Professional
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 Top5Note, 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/
AnthonyJosephHello 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 column bar chart grouped by rank (maximum values for all years is the highest rank, the second maximum values will be rank# 2,etc)
I need to display rank in x-axis with the "Values" in y-axis and "Year" as legend. Any help on how to achieve this is highly appreciated.
Table - Detail
Template no Attribute Category Value 1 Vehicle Bike 100 1 Vehicle Scooter 200 1 Vehicle Heavy vehicle 140 1 Vehicle bicycle 240 1 Vehicle car 50 1 Non-vechile Rocket 20 1 Non-vechile Helicopter 30 1 Non-vechile Airplane 45 1 Non-vechile Aircraft 56 1 Non-vechile Jet 70 2 Vehicle Bike 10 2 Vehicle Scooter 20 2 Vehicle Heavy vehicle 30 2 Vehicle bicycle 10 2 Vehicle car 50 2 Non-vechile Rocket 20 2 Non-vechile Helicopter 67 2 Non-vechile Airplane 87 2 Non-vechile Aircraft 23 2 Non-vechile Jet 49 3 Vehicle Bike 46 3 Vehicle Scooter 23 3 Vehicle Heavy vehicle 45 3 Vehicle bicycle 67 3 Vehicle car 12 3 Non-vechile Rocket 98 3 Non-vechile Helicopter 76 3 Non-vechile Airplane 34 3 Non-vechile Aircraft 22 3 Non-vechile Jet 18 Table Year:
Template no Year 1 2019 2 2020 3 2021 - Fowmy
AnthonyJoseph
Is this a duplicated post? I answered the question in the 1st post here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/show-top-5-based-on-category/m-p/2464378#M66906
- AnthonyJoseph
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.
- tackytechtom
Hi AnthonyJoseph ,
Another way could have been to use the filter pane like this:
Hope this helps! 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/ 
Whitewater100Solution Sage
Hi:
Do you have dates associated with the dtail table?
Thanks..
- AnthonyJoseph
No .... No dates table is associated or involved in this data model
- Whitewater100
Hi Anthony:
I hope this is what you need. Link here:
https://drive.google.com/file/d/1gJdZ6ToMoPuGY2qItJFeFLHdttcMQ4q_/view?usp=sharing
We can use Generate & Values to narrow it down:
Top 5 by Year = GENERATE( VALUES('Year'[Year]),TOPN (5,ADDCOLUMNS (VALUES ( 'Table'[Category] ),"Sales Amount", [Sales Amount]),[Sales Amount]))- AnthonyJoseph
THanks Whitewater100 . Appreciate your help. However, I think I m still facing the same issue. I can still see the gaps between bars in the x-axis. From my Dax solution I did achieved the same result and I m trying to get rid of the gaps between the bars. For example: 2019 has a gap before and after the pink bar. Similary for 2020 and 2021 there is at least one empty bar which I want to get rid off...
P.S. I do also understand the reason for those white gaps but dont know how to solve it 🙂
- Whitewater100
Hi:
Sorry wrong link:
https://drive.google.com/file/d/1gJdZ6ToMoPuGY2qItJFeFLHdttcMQ4q_/view?usp=sharing
Top 5 by Year = GENERATE( VALUES('Year'[Year]),TOPN (5,ADDCOLUMNS (VALUES ( Category[Category] ),"Sales Amount", [Sales Amount]),[Sales Amount])) - Whitewater100
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.
v-yalanwu-msftCommunity Support
Hi, AnthonyJoseph ;
Based on my research, I'm afraid it couldn't achieve in Power BI for now.
Clustered column chart aligns with each labels and group by legends, it will keep the space of blank items and not support auto fit them. I'd like to suggest you post this requirement to ideas.
You can do conditional formatting for value using a measure and using field value option.
https://community.powerbi.com/t5/Desktop/Hiding-blanks-in-Clustered-Column-Chart/td-p/1935415
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
