Sum of Rank Measures Output

[CDBIN]

I am new to BI and DAX, and have run into a wall - Normally I can find the answers with some research! I have a report with multiple columns displaying metric results.

I have ranked these results based on performance via the measure below:

Needed Rank = rankx(allselected(Query2[Site Selected]),CALCULATE(AVERAGE(Query2[Metric])),,asc)

There are 12 such ranking measures, one for each metric.

I am needing to get the sum of the Ranks by site to get a cumulative total of their ranking (which will then be ranked!) - However every function I attempt returns an aggragate number rather than the accurate return. 

One example is below, althogh I have tried many sum and calculate versions - cannot get it to function!

Total Rank = sumx(ALLSELECTED(Query2[Site]),[Metric Rank1]+[Metric Rank2])

This returns 130 for all sites, maximum possible return for the poorest site would be 26. 

[Anonymous]

Hi @CDBIN ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Dangar332 · Accepted solution]

hi, @CDBIN 

try below
make a column of average

avg. column = CALCULATE(AVERAGE(Query2[Metric])

then make a measure

measure=
 SUMX(
   FILTER(
     ALL('Table'),
    'Table'[avg. column] <=MAX('Table'[avg. column])
   ),
   [needed rank]
 )