Count price position in PowerBI

[Hidde]

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?

[v-xjiin-msft · Accepted 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.

[Hidde]

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

[vanessafvg]

@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

[Hidde]

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])))

[vanessafvg]

@Hidde

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

[Hidde]

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

[vanessafvg]

@Hidde

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

[Hidde]

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

[vanessafvg]

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

[Hidde]

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

[vanessafvg]

@Hidde does this work?

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