Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

How to generate TOP 5 based on Average and display in bar chart?

Hello Guys,

 

Below you see is a sample dataset. What I am trying to achieve is to generate TOP 5  for each attribute group by "Group ID". The TOP 5 values should be an average of the values that are taken into consideration. Hence, in our example, I would say that when a user selects Group-ID as 0, he should see  5 values as:

 

a. Wheat Production: 312 (Average of 400 and 224)

b. Maize: 205 (Average of 144 & 266)

c.  Rice: 100 (Average of 200 & 0)

d.  Flour: 87.5 (Average of 175 & 0)

e. <<Some other value>>

 

I treid using a rankX function in DAX. What i have written is mentioned as:

 

RANKX(ALL('table(2)'[Value]),CALCULATE(AVERAGE('table (2)'[Value])),,DESC)

I don't thik this working as it should. I am not sure what the formulae would be. Help needed!

Capture-1.PNG

1 REPLY 1
Anonymous
Not applicable

Hi @Anonymous ,

You can try to use following measure to calculate group average and do ranking on it:

Top 5=
VAR summary =
    SUMMARIZE ( Table, [Group ID], [Attribute], "AVG", AVERAGE ( Table[Value] ) )
VAR rank =
    RANKX ( summary, [AVG], AVERAGE ( Table[Value] ),DESC, DENSE )
RETURN
    IF ( rank <= 5, "Y", "N" )

You can use it in visual level filter to filter 'Y' result to display top 5 records.

Regards,

Xiaoxin Sheng

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.