Forum Discussion

JessicaWang_'s avatar
JessicaWang_
New Member
1 year ago

Ranking based on different measures and different dimensions

Hi Team, 

 

I am attempting to create the chart below in Power BI, but I've learned that it's not possible to create a calculated ranking function in PBI. Is there a workaround to achieve the desired chart?
Really appreciate and thanks a lot !

 

Dimensions :
1. Ranking 

2. Image

3. Selected Week and Selected Week -1

4. Item No

 

Measures: 

1. Sales $

2. Sold Qty 


Filters:

 

Tutorial Requests

3 Replies

  • saud968's avatar
    saud968
    Icon for Memorable Member rankMemorable Member
    1 year ago

    Create a Ranking Measure: Use the RANKX function to rank your items based on Sales or Sold Quantity. For example, to rank items based on Sales, you can create a measure like this:

    SalesRank = RANKX(ALL('YourTable'), CALCULATE(SUM('YourTable'[Sales])), , DESC, DENSE)
    Similarly, for Sold Quantity:

    QtyRank = RANKX(ALL('YourTable'), CALCULATE(SUM('YourTable'[Sold Qty])), , DESC, DENSE)
    Add the Measures to Your Visual: Add the SalesRank and QtyRank measures to your visual along with the other dimensions like Item No, Selected Week, and Selected Week -1.

    Use Conditional Formatting: To visually represent the ranking, you can use conditional formatting on the SalesRank and QtyRank measures.

    Create a Custom Table for Images: If you need to display images, create a custom table with Item No and the corresponding image URLs. Then, use a calculated column to link these images to your main table.

    Combine Everything in a Visual: Use a table or matrix visual to combine all these elements. You can add the Item No, Sales, Sold Qty, SalesRank, QtyRank, and images to the visual.

    Here's an example of how you might set up your table visual:

    Columns: Item No, Sales, Sold Qty, SalesRank, QtyRank, Image
    Rows: Selected Week, Selected Week -1
    By following these steps, you should be able to create a chart that ranks items based on different measures and dimensions in Power BI

    Best Regards
    Saud Ansari
    If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!


    • JessicaWang_'s avatar
      JessicaWang_
      New Member
      1 year ago

      Thank you for your response. I apologize for not being clearer. I would like to create it as a calculated dimension rather than a measure, and it should be placed in the first position of the metrics. 

      • saud968's avatar
        saud968
        Icon for Memorable Member rankMemorable Member
        1 year ago

        Create a Calculated Column for Ranking: Use the RANKX function to create a calculated column that ranks your items based on Sales or Sold Quantity. For example, to rank items based on Sales, you can create a calculated column like this:

        SalesRank = RANKX(ALL('YourTable'), CALCULATE(SUM('YourTable'[Sales])), , DESC, DENSE)
        Similarly, for Sold Quantity:

        QtyRank = RANKX(ALL('YourTable'), CALCULATE(SUM('YourTable'[Sold Qty])), , DESC, DENSE)
        Add the Calculated Column to Your Visual: Once you have created the calculated column, you can add it to your visual. Make sure to place it in the first position of the metrics.

        Adjust Your Visual: Add the SalesRank or QtyRank calculated column along with other dimensions like Item No, Selected Week, and Selected Week -1 to your visual.

        Here's an example of how you might set up your table visual:

        Columns: SalesRank, Item No, Sales, Sold Qty, Image
        Rows: Selected Week, Selected Week -1
        By following these steps, you should be able to create a chart that ranks items based on different measures and dimensions

        Best Regards
        Saud Ansari
        If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!