Ranking repetitive KPI's

[IanT76]

We have a set of KPI's that each business is assessed against and scored (out of 4.00) each month - example of data set;

I'd like to build a ranked table see we can see where each Business sits each moth, and see who's moving up/down in terms of performance as the year progresses.

I've used this formula; Rank = RANKX('SVP_Summary',('SVP_Summary'[Score]),,DESC,Dense) - but get the below output; the layout is correct, but I can't figure out why the BU's are not all displayed on a table of only 22 rows???

[v-juanli-msft · Accepted solution]

Hi @IanT76 

Create a column as below:

rank =
RANKX (
    FILTER ( 'Table', 'Table'[month] = EARLIER ( 'Table'[month] ) ),
    'Table'[score]
        + DIVIDE ( RANKX ( 'Table', [cate],, DESC, DENSE ), COUNTROWS ( 'Table' ) + 1 ),
    ,
    DESC,
    DENSE
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[IanT76]

Thanks @v-juanli-msft - perfect!

[vanessafvg]

maybe i m being a bit slow, but i am struggling to understand what you asking, what are you expecting?

[IanT76]

Hi - I'm expectign a table of 22 rows (we have 22 Businesses) and the Businesses will move up/down in each month's column based on their scores... this is how it currently looks in oue Excel-based version;

[amitchandak]

@IanT76 , I think this needs multiple unpivot ?

https://kohera.be/blog/power-bi/how-to-unpivot-twice/

Then you can use ranking

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[IanT76]

@amitchandak - Almost there!...

I've sorted the pivots and now have a 22-row table, with all Businesses in correct ranked order, courtesy of including the FILTER expression in the formula, based on Month; 

RANKX(FILTER('SVP_Summary','SVP_Summary'[Month]=EARLIER(SVP_Summary[Month])),'SVP_Summary'[Score],,DESC,Skip)

However, the table is not displaying all Business that have common scores;  compare January below from PowerBI and Excel ..positions 9, 10, 11 & 12 in BI are blank but would like these to display those tied Businesses (should be Repasa, Reyser, UAE Ports, Ukraine)....

How do I solve this last issue??

[IanT76]

Thanks Amit - let me review and revert. Cheers!