Search code examples
excelpowerbidaxpowerpivot

DAX Measure - Specific Ranking using RANKX

I have a table that just contains all articles. Then I have a transactional table that contains a value that I would like to use for the rank. THe same tables has the department information. The structure is like:

enter image description here

Now I would like to get the Ranking based in Value, in the end represented in a matrix.

This function shows me the ranking based on the value:

RANKX(ALLSELECTED(Article),[Value])

If the matrix just contains Article in the rows, then it works.

enter image description here

But when I add department, then the Rank is calculated for each department.

enter image description here

How can I get the RANK for each Article regardles of the Department?

Solution

  • I'm assuming that you are creating a measure and that you are putting the results in a "table", rather than a "matrix". If so, then the following DAX works for me.

    Rank = RANKX(ALLSELECTED(Table1), Calculate(Max(Table1[Value])))

    Note that this is when all of the data comes from a single "Table1". You may need to make updates to the measure above based on your table structure and names.

    enter image description here