RANKX issues need to change the grouping

[Anonymous]

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)

[TomMartens]

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

[Anonymous]

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

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may try below measure:

Rank =
RANKX (
    ALLSELECTED ( Emerging_for_PBI[Job_Title] ),
    [Growth_since_last_year],
    ,
    DESC,
    DENSE
)

Regards,