Forum Discussion

DFPRO's avatar
DFPRO
Frequent Visitor
2 years ago
Solved

RANKX and Slicing

I have a data set that includes "Name" and "Count" as a dimension table, and a fact table containing these Names and many Scores. I also have several Dim.Tables for various metrics, such as "Employee Classification". I have calculated average "Score" for each "Name" using a measure. I have created a ranking measure to rank the Names by the Scores: 

Rank = 
CALCULATE(
     RANKX(
          ALLSELECTED(Dim.Table[Name]),
          [Avg Score],,desc,dense),
     ALLSELECTED(Dim.Table2),
     ALLSELECTED(Dim.Table3))

This gives me the results below: 

NameRankAvg Measure
Employee 113.645
Employee 213.645
Employee 322.167
Employee 422.167
Employee 531.797
Employee 640.871
Employee 750.777
Employee 860.762
Employee 970.738
Employee 1080.724
Employee 1190.688
Employee 1290.688
Employee 1390.688
Employee 1490.688
Employee 1590.688
Employee 16100.631
Employee 17110.630
Employee 18120.594
Employee 19130.584
Employee 20140.536

 

This works as intended, and I have future measures that use tiebreakers which also works. However, when I add a slicer to the page for "Count", which is a calculated column in the Dim.Table, it changes the order of the ranking. The Count ranges from 0-22, and the slicer defaults to this range though none of the Employees have a Count of 0. When I move the slicer to a range of 1-22, the following happens: 

Employees 1-15 remain the same, but Employee 16 and 17 swap Ranks. This also happens with several other Employees further down in the table: 

 

Employee 1590.688
Employee 17110.630
Employee 16110.631
Employee 18120.594
Employee 19130.584
Employee 20140.536

 

Do we know why this is happening when the value it is ranking does not change? 

Need Help

2 Replies