Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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 )
Thanks,
Xi Jin.
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 )
Thanks,
Xi Jin.
This works perfectly, thanks! What does the EARLIER function do in this context?
@Hidde you can create a measure and then place your fields on a matrix or table
Price Ranking = RANKX (all ('table',CALCULATE(sum('table'[price])),,ASC)
obviousy your price amount needs to be a decimal or whole number
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 1 | Product 1 | 2 |
Client 2 | Product 1 | 8 |
Client 3 | Product 1 | 4 |
Client 1 | Product 2 | 1000 |
Client 2 | Product 2 | 2 |
Client 3 | Product 2 | 200 |
Tried this but I it does not have correct order:
Price position = RANKX(all(Table1),calculate(sum(Table1[Price]),values(Table1[Product])))
Price Ranking = RANKX (all ('Price Position'[Client],'Price Position'[Amount]),CALCULATE(sum('Price Position'[Amount])))
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).
Price Ranking = RANKX (all ('Price Position'[Client],'Price Position'[Amount]),CALCULATE(sum('Price Position'[Amount])),,DESC)
Proud to be a Super User!
Thanks, but still the first row has Ranking = 3, while it should have ranking =1
@Hidde then all you need to do is change it to ASC instead of DESC
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:
@Hidde does this work?
Price Ranking = RANKX (all ('Price Position'[Client]),CALCULATE(sum('Price Position'[Amount])),,ASC,Dense)
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |