Formula to rank/score staff based on sales amount

[KristofferAJ]

Hi,

I'm looking for a formula similar to what I have been using in excel PERCENTRANK.INC

This formula allows me to give each a score based on their own and their peers performance (relative standing of a value within a data set)

Unfortanately this function does not exist to my knowledge in powerbi. but is there any similar alternative.

Im not looking for a simple "rank" like 1,2,3,4,5,6, but instead the one sellling the most gets "100" and the one sells the fewest "1" then the others are distributed between those two values

Fruit	Account ID	Country	Sold amounts	Score/Rank
Pear	klara	Austria	234	79%
Pear	Konrad	Austria	45	29%
Pear	Mike	Austria	56	36%
Pear	Pia	Austria	67	43%
Orange	klara	Austria	12	0%
Orange	Konrad	Austria	345	93%
Orange	Mike	Austria	32	29%
Orange	Pia	Austria	57	57%
Apple	klara	Austria	3	7%
Apple	Konrad	Austria	8	29%
Apple	Mike	Austria	0	0%
Apple	Pia	Austria	5	21%
Pear	Eva	France	786	86%
Pear	John	France	789	93%
Pear	Shannon	France	80	57%
Pear	Simon	France	90	71%
Orange	Eva	France	234	79%
Orange	John	France	2345	100%
Orange	Shannon	France	64	64%
Orange	Simon	France	23	7%
Apple	Eva	France	3	7%
Apple	John	France	57	57%
Apple	Shannon	France	34	43%
Apple	Simon	France	456	71%
Pear	Chris	Germany	9	21%
Pear	Jim	Germany	6	7%
Pear	Liz	Germany	4	0%
Pear	Paul	Germany	4354	100%
Orange	Chris	Germany	65	71%
Orange	Jim	Germany	234	79%
Orange	Liz	Germany
Orange	Paul	Germany	32	29%
Apple	Chris	Germany	547	79%
Apple	Jim	Germany	678	93%
Apple	Liz	Germany	798	100%
Apple	Paul	Germany	645	86%
Pear	Beernard	Spain	67	43%
Pear	Karin	Spain	8	14%
Pear	Susan	Spain	89	64%
Orange	Beernard	Spain	23	7%
Orange	Karin	Spain	46	43%
Orange	Susan	Spain	24	21%
Apple	Beernard	Spain	345	64%
Apple	Karin	Spain	23	36%
Apple	Susan	Spain	34	43%

Thanks

Kristoffer

[v-chenwuz-msft · Accepted solution]

Hi @KristofferAJ  , 

I have tried the function PERCENTRANK.INC in excel. But the result what i got is not the same with your [Score/Rank].

You can try this expression to get the same result as PERCENTRANK.INC in power bi.

Column =
DIVIDE (
    RANKX (
        FILTER ( 'Table', NOT ( ISBLANK ( [Sold amounts] ) ) ),
        [Sold amounts],
        ,
        ASC
    ) - 1,
    COUNTROWS ( FILTER ( 'Table', NOT ( ISBLANK ( [Sold amounts] ) ) ) ) - 1
)

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[KristofferAJ]

Hi @v-chenwuz-msft !

Thanks for taking the time to support with this. I have tried a lot of options and I beleive what you have suggested is the best option.

Looking at the data you have provided the reason why it does not provide the same result is that I want the calculation to take the type of Fruit into account. I don't think it was clear from my request sorry for that.

In short: There will be a ranked #1 for each of the type of fruits across the countries

Would that be possible to built into the formula?

Thanks

Kristoffer