cancel
Showing results for
Did you mean:
Helper III

## Formula to rank/score staff based on sales amount

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

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Helper III

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