Forum Discussion
Solved
Getting equal quartiles from a ranking
I have a list of around 80.000 customers. I`m trying to rank them based on how often they have purchased. For this I've created a calculated column called Frequency. This is basically just a count of...
Hi aukev ,
Based on my test, you could use below formula to rank your data:
Frequency Rank = COUNTROWS(FILTER(ALL('magentoCustomers'),ISONORAFTER('magentoCustomers'[Frequency],SELECTEDVALUE(magentoCustomers[Frequency]),DESC,'magentoCustomers'[customerID],SELECTEDVALUE(magentoCustomers[customerID]),DESC)))Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
v-danhe-msft
The expected result would be this:
| customerID | Frequency | Rank | Quartile |
| 82770 | 7 | 2 | Q4 |
| 82771 | 3 | 4 | Q3 |
| 82773 | 0 | 9 | Q2 |
| 82774 | 11 | 1 | Q4 |
| 82775 | 6 | 3 | Q4 |
| 82776 | 3 | 5 | Q3 |
| 82777 | 0 | 10 | Q1 |
| 82778 | 1 | 7 | Q2 |
| 82779 | 1 | 8 | Q2 |
| 82780 | 0 | 11 | Q1 |
| 82781 | 0 | 12 | Q1 |
| 82782 | 2 | 6 | Q3 |
Hi aukev ,
Based on my test, you could use below formula to rank your data:
Frequency Rank = COUNTROWS(FILTER(ALL('magentoCustomers'),ISONORAFTER('magentoCustomers'[Frequency],SELECTEDVALUE(magentoCustomers[Frequency]),DESC,'magentoCustomers'[customerID],SELECTEDVALUE(magentoCustomers[customerID]),DESC)))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He