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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.