Finding the bottom two numbers in a mesaure

[osura_perera]

Hi, 

I have a measure calculating an average score for few a criteria-based items. I want to find the two worst numbers and return the heading it belongs. There are four buckets and four items per bucket. It should keep the filter on the main bucket. 

for example bucket 1 worst two areas in performance are service catalogue and simplicity to log a ticket. 

The values and the measurement are coming from a single table. 

In the common table, 

| Bucket 1 a | Bucket 2 a |

| Bucket 1 a | Bucket 2 b |

| Bucket 1 a | Bucket 2 c |

| Bucket 1 a | Bucket 2 d |     

| Bucket 1 b | Bucket 2 a |

| Bucket 1 b | Bucket 2 b |

| Bucket 1 b | Bucket 2 c |

| Bucket 1 b | Bucket 2 d |

[v-yiruan-msft · Accepted solution]

Hi @osura_perera ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below to get the rank value per bucket base on the average value

Rank = 
IF (
    ISINSCOPE ( 'Table'[Items] ),
    RANKX (
        CALCULATETABLE ( VALUES ( 'Table'[Items] ), ALLSELECTED ( 'Table'[Items] ) ),
        [Average value],
        ,
        ASC,
        DENSE
    )
)

2. Apply a visual-level filter with condition: Rank <=2

You can also refer the following blog to achieve it.

Filtering the Top 3 products for each category in Power BI

Best Regards