RANKX with Grouping, A single value for column 'Price' in table 'query' cannot be determined

[Walter7432]

I have a list of prices from different suppliers. Grouping is done by EAN.

My goal is to create a rank per EAN. Who is the cheapest 1,2,3,4.

I tried new measure and new column. Both give the same error.

Ranking column = RANKX(ALLEXCEPT(query,query[EAN Code]),query[Price])

Error

A single value for column 'Price' in table 'query' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

What am i missing?

Thanks

[v-yanjiang-msft · Accepted solution]

Hi @Walter7432 ,

According to your description, here's my solution.

Create a calculated column.

Ranking column =
RANKX (
    FILTER ( ALL ( query ), 'query'[EAN Code] = EARLIER ( query[EAN Code] ) ),
    query[Price],
    ,
    ASC
)

Or create a measure.

Ranking Measure =
RANKX (
    FILTER ( ALL ( query ), 'query'[EAN Code] = MAX ( query[EAN Code] ) ),
    CALCULATE ( SUM ( query[Price] ) ),
    ,
    ASC
)

Get the correct result.

I attach the sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

Use SUM(query[price]) instead of query[price]

[Walter7432]

Thanks for the reply. I made the changes to both typs column and measure:

• Ranking column = RANKX(ALLEXCEPT(query,query[EAN Code]),SUM(query[price]))
  If i use a new column, i get the error
  <pi>A circular dependency was detected: Query1[Ranking column].</pi> 

• Ranking Measure = RANKX(ALLEXCEPT(query,query[EAN Code]),SUM(query[price]))
  If i use a measure, another error pops up when i insert it into a visual

[PaulDBrown]

Oh I see...for the measure, try

Rank EAN =
VAR _sum =
    SUM ( query[price] )
RETURN
    RANKX ( ALLSELECTED ( query[EAN Code] ), _sum )

[Walter7432]

Formula works, but Rank is now same for all. 🙄

Data View

Visual

[PaulDBrown]

That's because you included another column in the visual. 

Rank EAN =
VAR _sum =
    SUM ( query[price] )
RETURN
    RANKX ( ALL ( query[EAN Code] , query[Name] ), _sum )