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

Thank you lbendlin 

Any suggestions on - why not all 20 are showing.  Some DETAIL and also SERVICE GROUP show only 19.

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.