Top 5 with Multiple Field/Criteria

[ash_cool10]

Hi There,

I have a table with follwing 4 fields

1- CAT

2- SubCAT

3- DetailCAT

4- Sales

I have created a Matrix Table with drill down on Cat--> SubCat-->DetailCat with  value is SUM(Sales)

I want to show Top 5 of CAT initially on the Matrix Tablec, then when I drill down to next level (SUBCAT), we want to see TOP 5 of SUBCAT, and then when we drill down to next level, we want to see TOP 5 of  DetailCAT

Can anyone please help if this is possible ?

Thanks in Advance.

Ashish

[Bhaskarkolli9]

MAG1
Consolidated Account Group
Customer Name
Material

We are also facing same problem can any one give solution if any.

[v-easonf-msft · Accepted solution]

Hi， @ash_cool10 

Please refer to the solution mentioned in this thread.

Filtering the Top 3 products for each category in Power BI 

Pleae try formulas as below:

Rank2 = 
IF (
    ISINSCOPE ( 'Table'[DetailCAT] ),
    RANKX (
        CALCULATETABLE (
            VALUES ( 'Table'[DetailCAT] ),
            ALLSELECTED ( 'Table'[DetailCAT] )
        ),
        [Sales Amount]
    ),
    IF (
        ISINSCOPE ( 'Table'[SubCAT] ),
        VAR SubcatSales = [Sales Amount]
        RETURN
            CALCULATE (
                RANKX ( VALUES ( 'Table'[SubCAT] ), [Sales Amount], SubcatSales ),
                ALLSELECTED ()
            ),
        IF (
            ISINSCOPE ( 'Table'[CAT] ),
            VAR CatSales = [Sales Amount]
            RETURN
                CALCULATE (
                    RANKX ( VALUES ( 'Table'[CAT] ), [Sales Amount], CatSales ),
                    ALLSELECTED ()
                )
        )
    )
)

Then filter the data based on the newly created ranking column.

Best Regards,
Community Support Team _ Eason