topic Re: RankX for number of columns in DAX Commands and Tips

RankX for number of columns

MCG — Wed, 02 Dec 2020 18:35:07 GMT

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

Re: RankX for number of columns

AlB — Wed, 02 Dec 2020 19:42:38 GMT

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

Re: RankX for number of columns

MCG — Wed, 02 Dec 2020 20:11:33 GMT

Thanks:)

That is what I also calculated...

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

Mcg

Re: RankX for number of columns

AlB — Wed, 02 Dec 2020 20:17:49 GMT

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

Re: RankX for number of columns

MCG — Wed, 02 Dec 2020 21:07:34 GMT

exactly what i was looking for:)

perfect!

greta thanks

mcg

Re: RankX for number of columns

CNENFRNL — Wed, 02 Dec 2020 21:41:07 GMT

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] ) ) ) )

Re: RankX for number of columns

MCG — Wed, 02 Dec 2020 22:25:17 GMT

That is also nice solution:)

I will keep it in mind

thx

Mcg