topic Re: show top 5 based on category in DAX Commands and Tips

show top 5 based on category

AnthonyJoseph — Tue, 19 Apr 2022 19:33:41 GMT

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

Re: show top 5 based on category

Fowmy — Tue, 19 Apr 2022 20:16:18 GMT

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

Re: show top 5 based on category

AnthonyJoseph — Tue, 19 Apr 2022 20:20:54 GMT

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 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

Re: show top 5 based on category

tackytechtom — Tue, 19 Apr 2022 20:40:11 GMT

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/

Re: show top 5 based on category

tackytechtom — Tue, 19 Apr 2022 20:41:15 GMT

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/

Re: show top 5 based on category

Fowmy — Tue, 19 Apr 2022 20:42:03 GMT

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

Re: show top 5 based on category

AnthonyJoseph — Wed, 20 Apr 2022 19:36:50 GMT

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.

Re: show top 5 based on category

Whitewater100 — Thu, 21 Apr 2022 01:16:06 GMT

Hi:

Do you have dates associated with the dtail table?

Thanks..

Re: show top 5 based on category

AnthonyJoseph — Thu, 21 Apr 2022 15:07:22 GMT

No .... No dates table is associated or involved in this data model

Re: show top 5 based on category

Whitewater100 — Thu, 21 Apr 2022 18:20:17 GMT

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 (

ADDCOLUMNS (

VALUES ( 'Table'[Category] ),

"Sales Amount", [Sales Amount]

[Sales Amount]

)

Re: show top 5 based on category

Whitewater100 — Thu, 21 Apr 2022 18:32:04 GMT

Hi:

Sorry wrong link:

https://drive.google.com/file/d/1gJdZ6ToMoPuGY2qItJFeFLHdttcMQ4q_/view?usp=sharing

Top 5 by Year = GENERATE( VALUES(

'Year'[Year]),

TOPN (

ADDCOLUMNS (

VALUES ( Category[Category] ),

"Sales Amount", [Sales Amount]

[Sales Amount]

)

Re: show top 5 based on category

AnthonyJoseph — Thu, 21 Apr 2022 18:49:01 GMT

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 🙂

Re: show top 5 based on category

Whitewater100 — Thu, 21 Apr 2022 19:06:10 GMT

Hi:

Can you use my file? It all works fine , here are several ways to plot below. You can change your X-Axis to Categorical and it looks a little better, but this chart is not the best for comparing values and is designed to keep the categories or continuous axis in a uniform way across all three years. You can see 2019 and 2021 look same for the first two categories becasue they are in same order. 2020 is not. In this case it's probably better to use a different visual like below. It's a design thing. These other visuaIs handle the type of order issues you are facing on this one chart. I have tested a bunch of format things and that chart is designed like it is behaving.I hope you mark this as solved.

Re: show top 5 based on category

Whitewater100 — Thu, 21 Apr 2022 20:54:31 GMT

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.

Re: show top 5 based on category

v-yalanwu-msft — Fri, 22 Apr 2022 07:51:57 GMT

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.