RankX for number of columns

[MCG]

Hi,

I have a simple data set:

category   brand   value

x               a           10

x               b           15

y               c            20

y               d            25

I need to create ONE measure that will give me ranking for category and brand but shown in two separate tables:

cat   value   rank

x      35       2

y      45       1

brand value   rank

a        10       4

b        15       3

... for the rest brands

Is it possible via one RankX measure?

thanks

Mcg

[AlB · Accepted solution]

@MCG 

Sorry, hadn't read properly.

Rank measure V2 =
SWITCH (
    TRUE (),
    ISFILTERED ( Table1[category] ), RANKX ( ALL ( Table1[category] ), CALCULATE ( SUM ( Table1[value] ) ) ),
    ISFILTERED ( Table1[brand] ), RANKX ( ALL ( Table1[brand] ), CALCULATE ( SUM ( Table1[value] ) ) )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MCG]

exactly what i was looking for:)

perfect!

greta thanks

mcg

[CNENFRNL]

Hi, @MCG , you might want to try to further integrate rankings this way,

Rank =
SWITCH (
    TRUE (),
    ISINSCOPE ( Table1[brand] ),
        RANKX (
            ALL ( Table1[brand] ),
            CALCULATE ( SUM ( Table1[value] ), ALL ( Table1[Category] ) )
        ),
    ISINSCOPE ( Table1[category] ), RANKX ( ALL ( Table1[category] ), CALCULATE ( SUM ( Table1[value] ) ) )
)

[MCG]

That is also nice solution:)

I will keep it in mind

thx

Mcg

[AlB]

Hi @MCG 

For your first visual:

Rank measure = 
RANKX(ALL(Table1[category]), CALCULATE(SUM(Table1[value])))

For the second:

Rank measure 2 = 
RANKX(ALL(Table1[brand]), CALCULATE(SUM(Table1[value])))

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MCG]

Thanks:)

That is what I also calculated...

What i wanted to achieve is to get same result in one measure...

br

Mcg