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

RANKX issues need to change the grouping

 

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)

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: RANKX issues need to change the grouping

Hi @Ian_IM 

You may try below measure:

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

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User
Super User

Re: RANKX issues need to change the grouping

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:

image.png
Hopefully this is what you are looking for.

 

Regards,

Tom

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Ian_IM Frequent Visitor
Frequent Visitor

Re: RANKX issues need to change the grouping

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

 

Community Support Team
Community Support Team

Re: RANKX issues need to change the grouping

Hi @Ian_IM 

You may try below measure:

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

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.