How to write a DAX expression where I can rank products within each category?

[Dawn7047]

I have a query table as follows:

For the DAX expression, I used a quick measure with the formula as follows:

ranking = RANKX(ALLSELECTED(Sheet1[products]),SUM(Sheet1[Current volume]))

The formula was supposed to Rank the products based on the Current Volume. To test this, I used a Pivot Table as the main visual and showed the values for Current Volume and Ranking side by side. The result were as follows:

I want to show the ranking for each product based on the given category. Is there any DAX expression I can use to do this?

[TomMartens]

Hey @Dawn7047 

the below screenshot shows the ranking for colors inside a brand:

Please be aware that also the Brands are ranked accordingly, I use this measure to create the above output:

Measure 4 = 
if( ISINSCOPE('DimProduct'[ColorName] )
    // the inner column (colorname) is ranked inside a group (brandname)
    , RANKX(
        CALCULATETABLE(
            SUMMARIZE(
                ALLSELECTED( 'FactOnlineSales' )
                , 'DimProduct'[BrandName]
                , 'DimProduct'[ColorName]
            )
            , VALUES( 'DimProduct'[BrandName] )
        )
        , CALCULATE( SUM( 'FactOnlineSales'[SalesAmount] ) )
    )
    // the outer column (brandname) is ranked
    , RANKX(
        CALCULATETABLE(
            SUMMARIZE(
                ALLSELECTED( 'FactOnlineSales' )
                , 'DimProduct'[BrandName]
            )
        )
        , CALCULATE( SUM( 'FactOnlineSales'[SalesAmount] ) )
    )
)

Hopefully this provides what you are looking for.

Regards,

Tom

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Ranking measure expected result: =
SWITCH (
    TRUE (),
    ISINSCOPE ( Data[Products] ),
        RANKX (
            ALL ( Data[Products] ),
            CALCULATE ( SUM ( Data[Current volumn] ) ),
            ,
            DESC
        ),
    ISINSCOPE ( Data[Category] ),
        RANKX (
            ALL ( Data[Category] ),
            CALCULATE ( SUM ( Data[Current volumn] ) ),
            ,
            DESC
        )
)