Forum Discussion

HappyG's avatar
HappyG
New Member
8 years ago

RANKX Calculated Measure

Hi,   I'm hoping someone can help!   I have a table 'Performance' which has the below columns:   Contract (number) SalesGroup (string) Month (date) Target (number) Actual (number)   There...
HappyG's avatar
HappyG
New Member
8 years ago

Hi,

 

Seriously stuck and in need of help please!

 

I have a table which has the following columns:

 

Contract (number)

SalesGroup (string)

Month (date)

Target (number)

Actual (number)

 

There are 25 contracts and 10 sales groups, but this could change.

 

From this the calculated measure PerfSum is created to give a dynamic % of target:

 

PerfSum:=IF(OR(SUM[Target])=0,SUM([Actual])=0),0,SUM([Actual])/SUM([TARGET])

 

I want to rank PerfSum so that it is ranks each contract 1-25 under each of the 10 SalesGroups and also each SalesGroup 1-10 against the others but can't seem to get RANKX to work.

 

Please Help!

MattAllington's avatar
MattAllington
Community Champion
8 years ago

The trick with RANKX is you need to use ALL. 

 

RANKX(ALL(table[salesgroup]),[measure])

 

if you dont use all, it will rank each item against itself and you always get 1.

 

ypu should always add table names in front of column names