Forum Discussion

CLNLP's avatar
CLNLP
Frequent Visitor
8 years ago
Solved

RANKX Ignore blank/zero across time

Hi

 

I am trying to rank yearly return and i am having problems with ranking different companies across time

 

I'm using the following measure:

 

RET_YTD_rank = 
IF(SUM(KONKURRENTER_RAPPORT[RET_YTD])<>0;
RANKX(
FILTER(
ALLSELECTED(KONKURRENTER_RAPPORT[FIRMA]);
sum(KONKURRENTER_RAPPORT[RET_YTD<>0);
CALCULATE(SUM(KONKURRENTER_RAPPORT[RET_YTD]))))

I get the following result in my Matrix:

 

Problem: Rank 4 and 5 is missing as the two firms with no value takes those places (even if it isn't shown in the matrix)

 

When i however remove 2015 in the Matrix, then everything looks fine:

 

So the problem is that it ranks the two firms with no data in 2011 when i include a year where they do have data

 

I am very new to Power BI, so i hope someone is able to help me out?

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI CLNLP,

     

    I think I succeed to prevent measure calculation on blank rows which expand by other category column:

    Measure =
VAR curr =
    CALCULATE (
        SUM ( [RET_YTD] ),
        VALUES ( KONKURRENTER_RAPPORT[FIRMA] ),
        VALUES ( KONKURRENTER_RAPPORT[DATO] )
    )
RETURN
    IF (
        curr <> 0,
        RANKX (
            FILTER (
                ALLSELECTED ( KONKURRENTER_RAPPORT ),
                CALCULATE ( SUM ( [RET_YTD] ), VALUES ( KONKURRENTER_RAPPORT[DATO] ) ) <> 0
            ),
            CALCULATE ( SUM ( [RET_YTD] ) ),
            ,
            ,
            DENSE
        )
    )

     

    Regards,

    Xiaoxin Sheng

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI CLNLP,

     

    I think this issue may related to your if condition, it replace some calculate result which calculated in rankx function as blank, so formula miss 4,5 rank numbers. 

     

    You can remove it to check full calculation result.

    IF(SUM(KONKURRENTER_RAPPORT[RET_YTD])<>0;

     

    Regards,

    Xiaoxin Sheng

    • CLNLP's avatar
      CLNLP
      Frequent Visitor
      8 years ago

      Hi Anonymous

       

      Thanks for your reply. Unfortunately it didn't change anything when i removed the IF clause

       

      Same result:

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi CLNLP,

         

        Can you share some sample data to test?

         

        BTW, you can also try to use following measure if it works:

        Result =
RANKX (
    FILTER (
        ALLSELECTED ( KONKURRENTER_RAPPORT );
        SUM ( KONKURRENTER_RAPPORT[RET_YTD] ) <> 0
    );
    CALCULATE ( SUM ( KONKURRENTER_RAPPORT[RET_YTD] ) )
)

        Regards,
        Xiaoxin Sheng