cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hidde Frequent Visitor
Frequent Visitor

Count price position in PowerBI

Hi, I am struggling with finding a solution for this:

 

I have a lot of prices per product, so the data looks like this:

 

Product - client - price - Price Position

Product 1 - Client 1 - €2 - 1
Product 1 - Client 2 - €8 - 3
Product 1 - Client 3 - €4 - 2

 

 

So I need to count the position in the ascending order of prices (lowest price = position 1, the best price position) per product. Can this be done in PowerBI?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xjiin-msft Super Contributor
Super Contributor

Re: Count price position in PowerBI

Hi @Hidde,

 

To achieve your requirement, you can try this calculated column:

 

Price Ranking =
RANKX (
    FILTER ( 'Rank', 'Rank'[Product] = EARLIER ( 'Rank'[Product] ) ),
    'Rank'[Price],
    ,
    ASC
)

1.PNG

 

Thanks,
Xi Jin.

View solution in original post

11 REPLIES 11
vanessafvg Super Contributor
Super Contributor

Re: Count price position in PowerBI

@Hidde you can create a measure and then place your fields on a matrix or tableCapture.PNG

 

Price Ranking = RANKX (all ('table',CALCULATE(sum('table'[price])),,ASC)

 

obviousy your price amount needs to be a decimal or whole number


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Hidde Frequent Visitor
Frequent Visitor

Re: Count price position in PowerBI

Thanks! Needs a little modification though because this only works if you have one product (I have multiple);

 

 

Client 1Product 12
Client 2Product 18
Client 3Product 14
Client 1Product 21000
Client 2Product 22
Client 3Product 2200

 

Tried this but I it does not have correct order:

 

Price position = RANKX(all(Table1),calculate(sum(Table1[Price]),values(Table1[Product])))

 

image.png

vanessafvg Super Contributor
Super Contributor

Re: Count price position in PowerBI

@Hidde

 

Price Ranking = RANKX (all ('Price Position'[Client],'Price Position'[Amount]),CALCULATE(sum('Price Position'[Amount])))

 

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Hidde Frequent Visitor
Frequent Visitor

Re: Count price position in PowerBI

Thanks, but with that the highest amount has the highest ranking (1). Highest amount (or price) should have lowest ranking (3).

vanessafvg Super Contributor
Super Contributor

Re: Count price position in PowerBI

@Hidde

 

Price Ranking = RANKX (all ('Price Position'[Client],'Price Position'[Amount]),CALCULATE(sum('Price Position'[Amount])),,DESC)


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Hidde Frequent Visitor
Frequent Visitor

Re: Count price position in PowerBI

Thanks, but still the first row has Ranking = 3, while it should have ranking =1 image.png

vanessafvg Super Contributor
Super Contributor

Re: Count price position in PowerBI

@Hidde then all you need to do is change it to ASC instead of DESC


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Hidde Frequent Visitor
Frequent Visitor

Re: Count price position in PowerBI

Tried that ofcourse, but doesnt work. It inverts something as now the ranking 4 is the lowest, but that should be 1:image.png

vanessafvg Super Contributor
Super Contributor

Re: Count price position in PowerBI

@Hidde does this work?

 

Price Ranking = RANKX (all ('Price Position'[Client]),CALCULATE(sum('Price Position'[Amount])),,ASC,Dense)


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)