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

did you implement my suggestion?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

lbendlin  Hi

I tried - but keep erroring out.  Here is my Rank measure.  Not sure where I would add RAND()

Rank_SvcDtl =

IF (

    ISINSCOPE(‘TABLE’[SVC_DTL]),

    RANKX(

        ALL(‘TABLE’[SVC_DTL]),     

        CALCULATE(SUM(‘TABLE’[Gross Rev Var]),

            FILTER(ALL(‘TABLE’[Gross Rev Var]),’TABLE’[Gross Rev Var]<>BLANK())),,DESC,Dense))

 

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Seems to work for me. The only thing that's off are the ranks for the  negative values.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you! I eagery opened the file.  Sadly, I have (my company) a Jan 2021 version of PBI.  and hence unable to open  your file.  Is there anyway you can just post the dax here, that you used.

      I had the issue with negative numbers also with a different version of dax I tried earlier.  At that time I was going to add an if statment for <0 and >0. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        I also tried to open this in PowerBI Service but Unfortunately seeing DAX behind a measure is not possible in power bi service and you can only see that in pbi desktop.

        lbendlin In PBiService I can see your ranking.  Looks good.  But if I can just view the DAX that would be great.

        Thanks for all your help.