Forum Discussion

mrslyfox's avatar
mrslyfox
Helper II
9 years ago

Ranking TopN Others

Hello,

 

Have two formulas to get 'Others' value for New Customer Volume by each customer.

The methodology working well with rest of 'Other' kpi's I have, but this one use copmlex measure and return zero.

 

RANK_NewCusM2_MEASURE = RANKX(ALLSELECTED(D_Customer[Customer]),CALCULATE(
    SUM(F_Invoice[M2]),
    FILTER(
        D_Customer,
        CALCULATE(COUNTROWS(F_Invoice))>0 &&
        CALCULATE(
           COUNTROWS(F_Invoice),
           FILTER(
               ALL(D_Date),
               D_Date[DATE_Date]< MIN(D_Date[DATE_Date])
            )
        )=0
    )))

 

Others Total NewCusM2 = IF( ISBLANK(CALCULATE(
    SUM(F_Invoice[M2]),
    FILTER(
        D_Customer,
        CALCULATE(COUNTROWS(F_Invoice))>0 &&
        CALCULATE(
           COUNTROWS(F_Invoice),
           FILTER(
               ALL(D_Date),
               D_Date[DATE_Date]< MIN(D_Date[DATE_Date])
            )
        )=0
    ),FILTER(all(D_Customer[Customer]),[RANK_NewCusM2_MEASURE]> 20))),0,CALCULATE(
    SUM(F_Invoice[M2]),
    FILTER(
        D_Customer,
        CALCULATE(COUNTROWS(F_Invoice))>0 &&
        CALCULATE(
           COUNTROWS(F_Invoice),
           FILTER(
               ALL(D_Date),
               D_Date[DATE_Date]< MIN(D_Date[DATE_Date])
            )
        )=0
    ),FILTER(all(D_Customer[Customer]),[RANK_NewCusM2_MEASURE]>20)))

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi mrslyfox,

     

    It is hard to test your formula without any sample data, can you please share some detail contents?

     

    Regards,

    Xiaoxin Sheng