cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HappyG Frequent Visitor
Frequent Visitor

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 are 25 Contracts and 10 SalesGroups, although this number could change.

 

There is a calculated measure which returns %age actual against target:

 

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

 

I want to rank each Contract's PerfSum 1-25 (or 1-x is less Contracts are selected) within each SalesGroup, and each SalesGroup 1-10 (1-x if less SalesGroups are selected) against the other SalesGroups within whatever parameters are selected but cannot get RANKX to work for this.

 

Any help would be greatly appreciated!

 

Thanks Smiley Very Happy

4 REPLIES 4
HappyG Frequent Visitor
Frequent Visitor

RANKX Calculated Measure

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!

Super User
Super User

Re: RANKX Calculated Measure

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Phil_Seamark Super Contributor
Super Contributor

Re: RANKX Calculated Measure

Hi @HappyG

 

Will the rankings change if you click various filters?  Or do you want he rankings to be locked down each time you refresh data into the model?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
HappyG Frequent Visitor
Frequent Visitor

Re: RANKX Calculated Measure

Hi @Phil_Seamark,

Ideally would like to update with selected parameters, but both dynamic and static measures would be useful to get an ‘overall’ and ‘within selexted’ Ranking.

Cheers.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 336 members 3,313 guests
Please welcome our newest community members: