Forum Discussion

leahschneider's avatar
leahschneider
Icon for Helper III rankHelper III
4 years ago

Rank Based on Group

I want to have a rank that works like any other measure, based on whatever qualitative column is in a table, not based on a predefined column.  For example. The two tables below are the rank and sum...
Need Help
DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
4 years ago

leahschneider,

 

This can be achieved with a SWITCH function that contains each possible dimension column.

 

Measures:

 

Sum Sales = SUM ( Sales[Sales] )
Sales Rank = 
SWITCH (
    TRUE,
    ISINSCOPE ( Products[Category] ), RANKX ( ALLSELECTED ( Products[Category] ), [Sum Sales],, DESC, DENSE ),
    ISINSCOPE ( Products[Color] ), RANKX ( ALLSELECTED ( Products[Color] ), [Sum Sales],, DESC, DENSE )
)

 

 

 

  • leahschneider's avatar
    leahschneider
    Icon for Helper III rankHelper III
    4 years ago

    That's still writing a dozen measures, it's just writing them all at once. I had seen this but it doesn't really meet my need. This doesn't work like any other aggregation, it only works on specified columns. If someone creates a new column and adds it in, it'll break this measure.