How to show the rank in comparison with a different measure?

Power BI
1

Hello. Is there a way to show in which rank of “quantity sold” is the product that has the highest profit/contribution margin?

image
image691×288 54.8 KB

For example: I’d like the card to show something like “Product A has the highest revenue, while having 30% of profit margin and beeing ranked 2 on quantity sold” (in this case, “30%” and “2” should be dynamic values).

Thanks in advance

Fluxo de Caixa.pbix (1.7 MB)

2

Bumping this post for more visibility from our experts and users.

3

Hi @klein.keagan - I have implemented on sample EDNA file. All Data is dynamic on the card visual. Please replicate it on your data. Let me know in case of any queries.

Output = var a = maxx(SUMMARIZE(Products,Products[Product Name],"Rev",[Total Sales]),[Rev])

var b = SUMMARIZE(filter(Products,[Total Sales] = a),Products[Product Name])

var c = FORMAT(CALCULATE([Profit Margin],Products[Product Name] = b),"Percent")

var d = maxx(filter(SUMMARIZE(values(Products[Product Name]),Products[Product Name],"Rnk",[Rank Products]),Products[Product Name] = b),[Rnk])

return

b & " has the highest revenue of " & a & ", while having " & c & " of profit margin and beeing ranked " & d & " on quantity sold"

image

UBG To Power BI - Model.pbix (701.2 KB)

Thanks
Ankit J

1 Like
4

Thanks a lot!
When I test it, I will post here and let you know.

I ask the mods to gently keep this thread open for at least a couple days. When I’m done, I will mark Ankit’s answer as the solution

5

Why use filter before summarize?

Thanks

6

Hi @klein.keagan -

If you are talking about
var b = SUMMARIZE(filter(Products,[Total Sales] = a),Products[Product Name]). It is needed to get value of Product Name from Products table having highest revenue.

for Var D = it is to filter the rank for [Product Name] extracted in Var b.

Thanks
Ankit J

7

Hi @klein.keagan

Just following up if you still require assistance to solve your inquiry.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

Hi. Sorry for about the delay. For some reason, it hid the names.
Here is the pbix
Fluxo de Caixa.pbix (1.7 MB)

9

Hi @klein.keagan - I have already shared the logic. Replicate in your pbix.

Can’t help further unless there is a specific query.

Thanks
Ankit J

1 Like
10

Hi @klein.keagan,

Just following up if the response above help you solve your inquiry.
If it did, please mark his answer as the SOLUTION.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

11

Hi @klein.keagan

Due to inactivity, we’d like to conclude that you no longer require assistance to solve your inquiry.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.