Forum Discussion
Post ProdigyExcluding dimenions within RANKX
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?
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!
1 Reply
- ValtteriN
Community Champion
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!