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

[Anonymous]

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!

[v-shex-msft]

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