Rank a column for each group but only for some cases

[valcat27]

Hello all,

I have a table where each line represents a transaction (a sale), whose prices I want to rank by product, but only for some cases.

This table is what I have now, but not what I want.
(The horizontal lines are only to better visualise the ranking of each product.)

 

This is my formula for rank column:

Rank =

VAR CurVal =  Table1[Price]

RETURN

CALCULATE(RANKX( Table1, Table1[Price], CurVal, ASC, Dense), FILTER(Table1,Table1[ProductID]=EARLIER(Table1[ProductID])))

Goal: to get the rank for each price (lower to highest), for each product… but there´s a catch: the flag. The flag is “on” when a certain characteristic is verified. I only care about ranking for a product if there is a transaction with the flag and if that transaction has the lowest price with the flag on.

Moreover, I would like to ignore prices equal zero (like ProductID 4) when ranking.

Therefore, the output that I want looks like this:

Can anyone help me?

Thanks in advance.

[v-deddai1-msft · Accepted solution]

Hi @valcat27 , 

You can create the following calculated column:

Rank =
VAR A =
    SUMMARIZE (
        'Table',
        'Table'[TranscationID],
        'Table'[ProductID],
        'Table'[Flag],
        'Table'[Price],
        "Rank",
            IF (
                'Table'[Price] = 0,
                BLANK (),
                RANKX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[ProductID] = EARLIER ( 'Table'[ProductID] )
                            && NOT ( 'Table'[Price] = 0 )
                    ),
                    'Table'[Price],
                    ,
                    ASC
                )
            )
    )
VAR b =
    SUMX (
        FILTER (
            A,
            'Table'[ProductID] = EARLIER ( 'Table'[ProductID] )
                && NOT ( ISBLANK ( 'Table'[Flag] ) )
        ),
        [Rank]
    )
RETURN
    IF (
        b = 1,
        SUMX (
            FILTER ( A, 'Table'[TranscationID] = EARLIER ( 'Table'[TranscationID] ) ),
            [Rank]
        ),
        BLANK ()
    )

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

Best Regards,

Dedmon Dai

[valcat27]

Hello @v-deddai1-msft , 

Thank you very much for your answer. It works exactly as I wanted.