Rank with different conditions

[yellow43]

Hello,

Hope you can help me.

I have a table and I would like to rank each row grouped by country and with 2 conditions. The conditions are as follows: 

1. same country, different products, 1st condition: max qty.
2. same country, different products, 1st condition: equal qty, 2nd condition: best sales amount.

Different dates may reflect different ranking.

The final result should be like this:

 

Can you please help me?

Thanks in advance.

 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a measure.

Rank measure: =
IF (
    HASONEVALUE ( Country[Country] ),
    CALCULATE (
        COUNTROWS ( Data ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            SUMMARIZE (
                ALL ( Data ),
                Country[Country],
                'Product'[Product],
                Data[Qty],
                Data[Sales Amount]
            ),
            ORDERBY ( Data[Qty], DESC, Data[Sales Amount], DESC ),
            KEEP,
            PARTITIONBY ( Country[Country] )
        )
    )
)

[yellow43]

Hello @Jihwan_Kim ,

Measure is not working when I select my table.

I only have a single table, should it worl the same way?

Rank measure: =
IF (
    HASONEVALUE ( 'Data'[Country] ),
    CALCULATE (
        COUNTROWS ( 'Data' ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            SUMMARIZE (
                ALL ( 'Data' ),
                'Data'[Country],
                'Data'[Product],
                'Data'[Qty],
                'Data'[Sales Amount]
            ),
            ORDERBY ( Data[Qty], DESC, Data[Sales Amount], DESC ),
            KEEP,
            PARTITIONBY ( 'Data'[Country] )
        )
    )
)

Thank you!