How to SUM a series of RANKS

drew61199 — Wed, 16 Nov 2022 04:27:21 GMT

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

Re: How to SUM a series of RANKS

daXtreme — Wed, 16 Nov 2022 07:54:34 GMT

Sample data please.

Re: How to SUM a series of RANKS

v-easonf-msft — Fri, 18 Nov 2022 09:43:08 GMT

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

topic Re: How to SUM a series of RANKS in DAX Commands and Tips

How to SUM a series of RANKS

Re: How to SUM a series of RANKS

Re: How to SUM a series of RANKS