Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |