Category fetching using RANK

[Anonymous]

We have a list of products and respective calculation. I need to fetch the top 3 categories on the basis of ranking of the products' calculation. Then create 3 separate graphs with top 3 products values, as given below:

We need to make sure, if there are 3 products with 2nd rank, first 2 are picked.

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I create a sample.

Here's my solution. For example, your logic for sorting products is total sales.

1.Create a calculated column Rank.

Rank =
RANKX (
    ALL ( 'Table' ),
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Category] = EARLIER ( 'Table'[Category] ) ),
        'Table'[Sales]
    ),
    ,
    DESC,
    DENSE
)

Get the rank column.

2.Put date and sales in a column chart, for the best selling, put Rank into the visual filter and select the value is 1.

In the same way, for the 2nd most selling, select the visual filter to 2. For the 3rd most selling, select the visual filter to 3. Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , best is to use a visual level filter on the rank

Create a rank like this and then use filter

Rankx(allexcept(Table, Table[Product]),[sales],,desc,dense)