Ranking based on different measures and different dimensions

[JessicaWang_]

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:

[saud968]

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_]

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]

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!