Ranking error with Rankx in PowerPivot using DAX

0
votes

Second day looking at DAX and abit stuck on this. I'm trying to getting a ranking system working, and at the basic level it works. But when I put groups in, it goes all wrong. Let me show you what I'm doing.

Say the table is called Table2 and contains the following data

Country City    Sales
Germany Munich  1
Germany T       3
Germany D       2
Germany Munich  7
Germany A       8
Germany Munich  6
Germany P       9

I would like to do a ranking by City. If I use this formulae I get the ranking overall

  =RANKX(all(Table2),Table2[Sales],,0)

Which just does a list from 1-7. However I would like Munich to be ranked number 1 etc. I have tried all sorts of forumlaes, for example =RANKX(all(Table2),Sumx(table2,Table2[Sales]),,0)

returns 1 in all columns. I reckon I need to group it by city, but anything I try with city gives an error that a table was expected in the formulae.

Hopefully this makes sense, as I would like to add another layer after this.

thanks

1
excel-2013powerpivotdax

1 Answers

1
votes

I think my main issue was that I was putting the formulae into a column rather than a calculated measure. Was the measure was created it was accessible through the table.