cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KristofferAJ
Helper III
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

 

FruitAccount IDCountrySold amountsScore/Rank
PearklaraAustria23479%
PearKonradAustria4529%
PearMikeAustria5636%
PearPiaAustria6743%
OrangeklaraAustria120%
OrangeKonradAustria34593%
OrangeMikeAustria3229%
OrangePiaAustria5757%
AppleklaraAustria37%
AppleKonradAustria829%
AppleMikeAustria00%
ApplePiaAustria521%
PearEvaFrance78686%
PearJohnFrance78993%
PearShannonFrance8057%
PearSimonFrance9071%
OrangeEvaFrance23479%
OrangeJohnFrance2345100%
OrangeShannonFrance6464%
OrangeSimonFrance237%
AppleEvaFrance37%
AppleJohnFrance5757%
AppleShannonFrance3443%
AppleSimonFrance45671%
PearChrisGermany921%
PearJimGermany67%
PearLizGermany40%
PearPaulGermany4354100%
OrangeChrisGermany6571%
OrangeJimGermany23479%
OrangeLizGermany  
OrangePaulGermany3229%
AppleChrisGermany54779%
AppleJimGermany67893%
AppleLizGermany798100%
ApplePaulGermany64586%
PearBeernardSpain6743%
PearKarinSpain814%
PearSusanSpain8964%
OrangeBeernardSpain237%
OrangeKarinSpain4643%
OrangeSusanSpain2421%
AppleBeernardSpain34564%
AppleKarinSpain2336%
AppleSusanSpain3443%

 

Thanks

Kristoffer

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

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.

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors