Hi
So I have a rankx formula and a previousquarter function, and it is producing logical results depending on the sort order, but not what I want. Ha!
It works fine when DESC, but treats a blank in the previous quarter as ranked 1 (but inserts a blank as that is what I have coded, when ASC and starts the previous quarter ranking at 2 for the others.
I want the PQ Rank not to include the blank. and for customers S T and U to be ranked #1 in the previous quarter (as they actually were #1 in the previous quarter, until customer A came along in the latest quarter).
Any assistance to ignore the PQ ranking for Customer A and start at 1 would be greatly appreciated.
Customer A had no data previous quarter, and when the rank is sorted DESC the results are like this.
| Customer | KPI value | KPI Rank | PQ KPI rank | Rank change |
| H | 5 | 7 | 4 | +3 |
| G | 10 | 3 | 3 | 0 |
| F | 8 | 4 | 3 | +1 |
| E | 21 | 1 | 3 | -2 |
| D | 5 | 7 | 3 | +4 |
| C | 10 | 3 | 2 | +1 |
| B | 16 | 2 | 1 | +1 |
| A | 5 | 7 | | |
and when the rank is sorted ASC I get these results.
| Customer | KPI value | KPI Rank | PQ KPI rank | Rank change |
| Y | 2 | 2 | 3 | -1 |
| X | 2 | 2 | 3 | -1 |
| W | 3 | 3 | 3 | 0 |
| V | 3 | 3 | 3 | 0 |
| U | 1 | 1 | 2 | -1 |
| T | 1 | 1 | 2 | -1 |
| S | 1 | 1 | 2 | -1 |
| A | 5 | 5 | | |
But what I want is
| Customer | KPI value | KPI Rank | PQ KPI rank | Rank change |
| Y | 2 | 2 | 2 | 0 |
| X | 2 | 2 | 2 | 0 |
| W | 3 | 3 | 2 | 0 |
| V | 3 | 3 | 2 | +1 |
| U | 1 | 1 | 1 | 0 |
| T | 1 | 1 | 1 | 0 |
| S | 1 | 1 | 1 | 0 |
| A | 5 | 5 | | |
I am using
KPI Rank =
IF(
ISBLANK([Average KPI value]),
BLANK(),
RANKX(
ALLSELECTED(Customers[Customer]
),
[Average KPI value],,ASC,Dense)
PQ KPI rank =
CALCULATE(
[Rank],
PREVIOUSQUARTER('Calendar'[Date])
)
Any assistance much appreciated.
Regards
John
