Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Category fetching using RANK

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.
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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)

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.