Excluding dimenions within RANKX

[joshua1990]

Hi experts!

Let's say I have table that contains a unique list of products as a dimensional table.

TableAtr

Product

A1

A2

B1

Then I have a second table that contains transactional data like this:

TableTrans

Product	Value	Department
A1	50	A
A2	60	B

Now, I would like to get the rank based on the Colum Value.

To get this number, we can use RANKX

RANKX(ALLSELECTED(TableAtr), [Value])

This works perfectly well when in the end we have a matrix that just shows Product

Product	Value	Rank
A1	50	2
A2	60	1

But when I add the department within the row, then rank is executed for each department/ group:

Product	Department	Rank
A1	A	1
A2	B	1

I would like to execute the RANKX just on Product level, but also showing the department.

How is this possible?

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

ranking with dep = RANKX(ALL('Rank with dep'),

calculate(SUM('Rank with dep'[Value]),ALL('Rank with dep'[Department])),,DESC)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!