Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hidde
Helper I
Helper I

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
v-xjiin-msft
Solution Sage
Solution Sage

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
v-xjiin-msft
Solution Sage
Solution Sage

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.

This works perfectly, thanks! What does the EARLIER function do in this context?

vanessafvg
Super User
Super User

@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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

@Hidde

 

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

@Hidde

 

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

@Hidde does this work?

 

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.