Table with rank position for selected slicer value

[NewbNeedHelp]

Hi all! I'm trying to figure out how to display a brand's ranking across a variety of columns.

I am a PowerBI noob and tried to find an article that matched this use case but couldn't quite figure it out. I tried making a RankX measure, but it ends up showing as "1" in the table for each of these 3 combinations (see illustration below), as it's filtering to the Brand selected in the slicer...and of course if it's filtered to just that 1 brand, that brand will be #1 in and of itself.

How can I keep the rank to be among all brands in the data set, but only show it for 1 brand?

Thank you for any help!

[v-zhangti]

Hi, @NewbNeedHelp 

You can try the following methods.

Rank Measure =
RANKX ( FILTER ( ALL ( 'Table' ),
        [Category] = SELECTEDVALUE ( 'Table'[Category] )
            && [Attribute] = SELECTEDVALUE ( 'Table'[Attribute] )
    ),
    CALCULATE ( SUM ( 'Table'[Dollars] ) ),
    ,
    DESC
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[NewbNeedHelp]

This is great - Thank you!

One thing I didn't mention is that some of these are in different tables.

• Dollars is in a Fact table with 2 columns: SKU (distinct) and Dollars.
• Category is in Dim1 table with 3 columns: SKU (distinct), Brand (not distinct), Category (not distinct).
• Attribute is in Dim2 table with 2 columns: SKU (not distinct) and Attribute (not distinct).

How would that change the RankX measure? Sorry I didn't give this information at first, I was trying to simplify my question :-/.

Thank you!

[v-zhangti]

Hi, @NewbNeedHelp 

You can try the following methods.

In Dim1 table:

Column:

Attribute = RELATED('Dim2 table'[Attribute])

Dollars = RELATED('Fact table'[Dollars])

Measure:

Rank Measure = 
RANKX ( FILTER ( ALL ( 'Dim1 table' ),
        [Category] = SELECTEDVALUE ( 'Dim1 table'[Category] )
            && [Attribute] = SELECTEDVALUE ( 'Dim1 table'[Attribute] )
    ),
    CALCULATE ( SUM ( 'Dim1 table'[Dollars] ) ),
    ,
    DESC
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[NewbNeedHelp]

Unfortunately it doesn't quite work because each SKU can have MANY attributes, not just 1.

[ryan_mayu]

@NewbNeedHelp 

you can try to create a rank column

rank = RANKX(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])&&'Table'[Attribute]=EARLIER('Table'[Attribute])),'Table'[Dollars])

pls see the attachment below