Custom Ranking & Scatter Plots

[Cali_2020]

Hello!

I would love to achieve a scatter plot that can be filtered based on a ranking. I have looked at many youtube videos and spent hours on AI but I still can't achieve the right results. It's very simple in Excel but it doesn't seem to work in PBI. If you can help me, I'd forever be grateful!

The goal a scatter plot that is based on a [custom] ranking system which focuses in on one product ID. The x-axis are quantities and y-axis is the price that they were sold at.

The [custom] ranking means that the product IDs are ranked by highest quantities sold to lowest quantities. In case of tie, then the second condition takes into consideration the product name. The rankx function doesn't seem to work like this (to the best of my knowledge).

The condition of the scatter plot and the ranking system is that it needs to take into respond to any slicers on the page level. I have tried using parameter tables but these don't respond to slicers (to the best of my knowledge). 

Attached is a visual of what I'd like to achieve.

Thank you!

[v-priyankata]

Hi @Cali_2020 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @Cali_2020 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @Cali_2020 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Cali_2020]

Hello, would this link to google sheets work? 

https://docs.google.com/spreadsheets/d/1h7movtX94lEyUmgf6bG-nqLgZ7TbeY1PsGvlzsLFbUA/edit?usp=sharing

[lbendlin · Accepted solution]

Here's some basic implementation

you will notice that the R measure can also serve as a visual level filter.

[Cali_2020]

How can you use these functions to account for two criteria? And does a disconnected reference table dynamically change based on page-level slicers?

[Cali_2020]

Hello, below is the data. The desired result is to create a bubble chart with quantities on the x-axis and price on the y-axis but isolated for the specific ranking at the visual level.

The ranking is based on the largest to smallest quantities for each product ID. However, there could be ties on the ranking so this could create an obstacle if using a visual-level filter. The logic that I'd like to use to break the tie is based on alphabetical order. That is, first the aggregation of quantities and then the if there is a tie, the first in line by alphabet takes the first order ranking. For example, product IDs ABC3 and ABC5 are tied in quanties but as ABC3 is higher up the alphabet than ABC5 then ABC3 is ranked prior to ABC5. I hope this make sense. To the best of my knowledge, the rank() or rankx() functions can account for two parameters for ranking. 

Thank you!

product ID	quantities	price	ranking
ABC1	10	1.5	Rank 4
ABC1	5	1.55	Rank 4
ABC1	3	1.53	Rank 4
ABC1	15	1.45	Rank 4
ABC1	5	1.59	Rank 4
ABC2	1	2.1	Rank 5
ABC2	1	1.95	Rank 5
ABC2	3	2.05	Rank 5
ABC3	18	2.65	Rank 2
ABC3	2	2.85	Rank 2
ABC3	4	2.75	Rank 2
ABC3	8	2.7	Rank 2
ABC3	20	2.6	Rank 2
ABC3	5	2.75	Rank 2
ABC3	15	2.65	Rank 2
ABC4	40	5	Rank 1
ABC4	25	5.3	Rank 1
ABC4	30	5.15	Rank 1
ABC5	25	2.25	Rank 3
ABC5	22	2.22	Rank 3
ABC5	25	2.24	Rank 3

[lbendlin]

instead of RANKX use the RANK function which allows sorting by more than one column.

Consider using a disconnected reference table with prepopulated rank options.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...