Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

More than 1 Rankx

Please see attached data. Attached screenshot is this report in Excel and the end result I am looking for.   First, sort service detail descending then pick the bottom 20 Second...
Anonymous's avatar
Anonymous
Not applicable
4 years ago

lbendlin 

Thank you.  Is there a more user friendly version 🙂 ?  The PowerQuery section of the code is beyond my skillset.  Per your earlier suggestion, if I wanted to add a small random value how would you envision it in the dax formula? (so that I can break the tie).

lbendlin's avatar
lbendlin
Super User
4 years ago

Add a calculated column instead 

 

GR = 'Table'[Gross Revenue]+RAND()/1000

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    lbendlin 

    The following is almost working

    Rank_SvcDtl =

    IF (

        ISINSCOPE(‘TABLE’[SVC_DTL]),

        RANKX(

            ALL(‘TABLE’[SVC_DTL]),     

            CALCULATE(SUM(‘TABLE’[GrossRev2]),

            FILTER(ALL(‘TABLE’[GrossRev2]),’TABLE’[GrossRev2]<>BLANK())),,ASC,Dense))

     

    GrossRev2 = ‘TABLE’[Gross Rev Var] + (RAND()/1000)

     

    here is the problem - (the following is similar fields, but different numbers)

    Rank 12 is missing.  This is throwing the total off, and then of course the sort order is off too.

     

    How can I tweak the dax, to make sure there is no skip in rank.  I think that should solve the problem.

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Any thoughts .... anyone???