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.
Job_Title Year Growth_since_last_year Rank_per_year Desired_rank
Chef 2010 3% 3 2
Chef 2011 10% 2 2
Chef 2012 20% 1 2
Accountant 2010 25% 3 1
Accountant 2011 30% 2 1
Accountant 2012 68% 1 1
I am trying to get the rank per year based on growth.. As you can see im getting the rank per year but it is taking the Job_title in consideration, in other words is constraining to the title when I want the rank per year without considering the title. Any help is greatly appreciated.
PLease see below the DAX im using ( My table is called Emerging_for_PBI and [Growth_since_last_year] is a calculated measure)
Rank = RANKX(ALL(Emerging_for_PBI[POSTING_YEAR].[Year] ), [Growth_since_last_year], , DESC , Dense)
Solved! Go to Solution.
Hi @Anonymous
You may try below measure:
Rank = RANKX ( ALLSELECTED ( Emerging_for_PBI[Job_Title] ), [Growth_since_last_year], , DESC, DENSE )
Regards,
Hey,
this DAX statement:
_Rank = RANKX( ALLSELECTED('Table3'[Job_Title]) ,CALCULATE(SUM(Table3[Growth_since_last_year]),ALL('Table3'[Year])) )
allows to create a table that looks liks this:
Hopefully this is what you are looking for.
Regards,
Tom
Hi Tom,
Thanks fot taking time to help me out. The issue is that Growth sisnce last year is a measure, and Im getting a error message saying 'The SUM function only accepts a column reference as the argument number 1. I tried passing the growth measure to create a column in power query but failed. How can I replace the Calculate sum for a measure?
Please see the measure below:
Growth_since_last_year =
IF(
ISFILTERED('Emerging_for_PBI'[POSTING_YEAR]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR =
CALCULATE(
AVERAGE('Emerging_for_PBI'[COUNT]),
DATEADD('Emerging_for_PBI'[POSTING_YEAR].[Date], -1, YEAR)
)
RETURN
DIVIDE(AVERAGE('Emerging_for_PBI'[COUNT]) - __PREV_YEAR, __PREV_YEAR)
)
Hi @Anonymous
You may try below measure:
Rank = RANKX ( ALLSELECTED ( Emerging_for_PBI[Job_Title] ), [Growth_since_last_year], , DESC, DENSE )
Regards,
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |