Hi everyone.
I'm trying to do a ranking inside Excel's data model or PQ for the use with power pivot.
Now, the way I do it with Excel is I make a percentile table using percentile.inc for 0%-100% then index match with less than for each value in the data set for each metric. Then multiply it by the weight. That gives me the score for each metric. I'd then sum all scores for all metrics to get a final one.
On the side, and for the final score, I run a quartile table where it'd provide the percentage of each quartile in relation to all the final scores.
After that, I do a lookup to print the quartile rank next to the name. You can see it in the sample data file I shared. I store the results in a different table for historical view - month over month.
https://docs.google.com/spreadsheets/d/1b71AFR65GQIdYNeLUKh5LKqVpHUdM9HD/edit?usp=drivesdk&ouid=1125...
What is the way of doing this inside the data model/power pivot?
