How to SUM a series of RANKS

[drew61199]

My dataset consists of a list of facilities and several metrics. I rank the metrics individually amongst the facilities and then sum the ranks for a "composite score".....ie.....facility A may get a 10, facility B a 15, etc. Finally, I rank the composite score and this is ultimately how we identify a top opportunity facility (easier for mgmt to understand '1st' or '2nd' than a random number for a composite score). 

I am now attempting a trend with an "overall top opportunity" (below). I simply included the "composite rank" in a matrix by facility and date. I expected it to 'subtotal' based on the series of ranks, but instead, it appears that it is somehow re-ranking all of the individual KPIs rolled up based on the date range selected. As you can see, I would expect the 5th row to be #2 overall. Is there a way to simply SUM or AVG the ranks? I cannot seem to figure out the syntax. Thanks in advance

[daXtreme]

Sample data please.

[v-easonf-msft · Accepted solution]

Hi, @drew61199 

Please try formula like:

Rank =
VAR _tab =
    SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Product], "_total", [total] )
VAR _rank =
    RANKX ( _tab, [total],, ASC, DENSE )
RETURN
    IF ( HASONEVALUE ( 'Table'[Date] ), SUM ( 'Table'[Value] ), _rank )

Best Regards,
Community Support Team _ Eason