Hello,
I created a few DAX measures to create 5 bins that all have the same number of records each time. Here are those measures.
Score Rank = RANKX(ALLSELECTED(PredictedScores),CALCULATE(SUM(PredictedScores[Score])),,0)
Quintile = DIVIDE([Score Rank],CALCULATE(COUNTROWS(PredictedScores),ALLSELECTED(PredictedScores)))
Quintile Binning =
IF([Quintile] <=.2, 1,
IF([Quintile] <= .4, 2,
IF([Quintile] <= .6, 3,
IF([Quintile] <= .8, 4,
IF([Quintile] <=1, 5,100)))))
Basically what Im doing with the DAX is ranking all the scores within my table, then binning those scores based off of their rank. I needed the AllSelected because im constantly filtering what scores will be binned.
The PredictedScores table looks something like this.
AccountID | Score
1 | .34
2 | .29
3 | .37
4 | .50
5 | .93
6 | .58
7 | .17
8 | .69
9 | .89
10 | .95
When I have all of the scores listed out, the correct binning is displayed next to each score, but when I make a new table visual with the Quintile Binning next to the Count of Account ID, instead of giving a breakout of the number of accounts in each bin, im left with all of the bins aggregated into one. Any thoughts on this? Thank you very much.
What I'd Like
Quintile Bin | Count of AccountID
1 | 2
2 | 2
3 | 2
4 | 2
5 | 2
What actually appears (I don't want)
Quintile Bin | Count of AccountID
1 | 10
