Forum Discussion

avaldiviezo's avatar
avaldiviezo
Icon for Advocate II rankAdvocate II
2 years ago
Solved

new column with ranking

Hello, I'm t creating a ranking by state or location, and based on the largest monetary amount, but when the monetary amount is the same in several cases in the corresponding version, skip 1   , departamento = state , MontoActualizadoPIP_Decimal = is money or number and the bd is    oeee VW_ReporteDetalladoMonitoreo, I need it to be correlative and not duplicate

 
RANKX(
    FILTER(
        ALL('oeee VW_ReporteDetalladoMonitoreo'),
        'oeee VW_ReporteDetalladoMonitoreo'[Departamento] = EARLIER('oeee VW_ReporteDetalladoMonitoreo'[Departamento])
    ),
    'oeee VW_ReporteDetalladoMonitoreo'[MontoActualizadoPIP_Decimal],
    ,
    DESC
)
 

hope someone can helpme 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, avaldiviezo 

    According to your description, you want to create rankings based on the largest currency amount, but in the corresponding version, if the currency amount is the same, it will skip the repeated ranking. You can try the following DAX expression:

    RANKX (
    FILTER (
        ALL ( 'oeee VW_ReporteDetalladoMonitoreo' ),
        'oeee VW_ReporteDetalladoMonitoreo'[Departamento]
            = EARLIER ( 'oeee VW_ReporteDetalladoMonitoreo'[Departamento] )
            && 'oeee VW_ReporteDetalladoMonitoreo'[MontoActualizadoPIP_Decimal]
                <> EARLIER ( 'oeee VW_ReporteDetalladoMonitoreo'[MontoActualizadoPIP_Decimal] )
    ),
    'oeee VW_ReporteDetalladoMonitoreo'[MontoActualizadoPIP_Decimal],
    ,
    DESC
)

    In the expression, I added a new condition in Filter for filtering repeated items. I hope this expression can help you.

     

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    could you pls provide some sample data? if the amount is duplicated , then what's the expected output?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, avaldiviezo 

    According to your description, you want to create rankings based on the largest currency amount, but in the corresponding version, if the currency amount is the same, it will skip the repeated ranking. You can try the following DAX expression:

    RANKX (
    FILTER (
        ALL ( 'oeee VW_ReporteDetalladoMonitoreo' ),
        'oeee VW_ReporteDetalladoMonitoreo'[Departamento]
            = EARLIER ( 'oeee VW_ReporteDetalladoMonitoreo'[Departamento] )
            && 'oeee VW_ReporteDetalladoMonitoreo'[MontoActualizadoPIP_Decimal]
                <> EARLIER ( 'oeee VW_ReporteDetalladoMonitoreo'[MontoActualizadoPIP_Decimal] )
    ),
    'oeee VW_ReporteDetalladoMonitoreo'[MontoActualizadoPIP_Decimal],
    ,
    DESC
)

    In the expression, I added a new condition in Filter for filtering repeated items. I hope this expression can help you.

     

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.