topic Re: Sum of Rank Measures Output in DAX Commands and Tips

Sum of Rank Measures Output

CDBIN — Tue, 12 Dec 2023 15:30:26 GMT

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.

Re: Sum of Rank Measures Output

Dangar332 — Tue, 12 Dec 2023 18:10:12 GMT

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] )

Re: Sum of Rank Measures Output

Anonymous — Fri, 15 Dec 2023 03:10:32 GMT

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