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...
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???