Creating a Rank for each Item By Custumer

[aquanr18]

Hi! tnx a lot for reading my post, 

My goal is to obtain the row TOP in powerr bi using rankx, 

This is a rank for each item by customer in the same table. 

My data model looks like this. 

any Help will be much appreciated, 

Thanks!

[LivioLanzo]

I guess you wanna do it as a calculated column:

Ranking = 
    VAR Sls = Sales[Sales]
RETURN
    CALCULATE(
        RANKX( 
            Sales, 
            Sales[Sales],
            Sls
        ),
        ALLEXCEPT( Sales, Sales[Cliente] )
    )

[aquanr18]

Hi! thanks ! but the formula has something wrong
I got the error "A single value form column 'Sales' in the table 'Sales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without an aggregation such as min, max, count or sum to get a single result'

I tryed to agreggate the sales by SUM but got nothing out of the formula. 

What should I do? 

[LivioLanzo]

@aquanr18

are you using the formula in a measure or calculated column ?

[aquanr18]

I have try both, in the measure i got the previus error, 

If I use a calculated the error is 'Function 'RANKX' dose not support comparing values of type text with values of type integer, consider using the value or format function to convert one of the values' 

[LivioLanzo]

@aquanr18

can you post the table on which you apply this formula via a calculated column

[aquanr18]

Here you can see the table I'm using

https://drive.google.com/open?id=1VNrH9QlZ6BKpP4NbC08qrmYJbVCi_o17

[PattemManohar · Accepted solution]

@aquanr18 Please try this as a "New Column"

Top = RANKX(FILTER(Test58Rank,Test58Rank[Client]=EARLIER(Test58Rank[Client])),Test58Rank[Sales],Test58Rank[Sales],DESC)

[aquanr18]

Thanks ! this is exactly what I wanted, may you explain how does the formula work? 

[PattemManohar]

@aquanr18 That's cool !!

Just to make it simple - If you are from SQL background, It is just similar to RANK() OVER() along with PARTITION BY. 

So here we want to rank for each group (in this case client) that's why we have nested the FILTER particular client records (each group) and then assigning a rank. To identify current row group, EARLIER function came into picture. That is it.... May be official Microsoft Documentation will explain it better