cancel
Showing results for
Did you mean:
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
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
)```

Thanks,
Xi Jin.

11 REPLIES 11
Super Contributor

## Re: Count price position in PowerBI

@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 a to be a Datanaut!
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 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])))

Super Contributor

## Re: Count price position in PowerBI

@Hidde

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

Proud a to be a Datanaut!
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).

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)

Proud a to be a Datanaut!
Frequent Visitor

## Re: Count price position in PowerBI

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

Super Contributor

## Re: Count price position in PowerBI

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

Proud a to be a Datanaut!
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:

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)

Proud a to be a Datanaut!

Announcements

#### 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

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

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)