RANX gives results are inconsistent, don't match export and give duplicates

[gspollock]

I am having several problems with a measure using RANKX.  This is the measure:

Rank Column = RANKX( ALLSELECTED(DIM_CUST_SLS_HIER[Account #]), FACT_SALES[FYTD GP TD],,DESC,Dense)

When the table has just the Rank Column measure and the columns that are used in the function, it is showing the same rank more than once in some cases.  For example here it has the 2nd ranked Account incorrectly showing as 3.

A second issue is that the csv export doesn't show the same results as the visual.  For example account 55024511 is different between the two.    It is 40 in the table and 41 in the export. Account 20144705 is rank 55 in the visual and 54 in the export.

Account #	Rank Column	FYTD GP TD
90731443	1	8,411,069.86
3751962	3	1,872,477.40
575850	3	670,648.16
390429	4	512,920.19
61658640	5	481,438.49
575835	6	442,578.48
58786022	7	434,445.96
60541621	8	358,635.43
20013397	9	327,711.49
3832376	10	325,860.82
60650191	11	318,105.55
54365537	12	315,152.14
61739770	13	303,790.18
20090861	14	291,383.16
733216	15	276,359.18
61739766	16	263,645.34
54513183	17	259,155.80
4306026	18	255,854.17
54412259	19	228,501.03
2491218	21	225,174.51
58876696	21	226,095.96
58876759	22	218,998.72
61772090	23	217,463.57
54271799	24	214,085.70
62734774	25	213,866.53
54363445	26	213,138.24
54411408	27	211,371.54
20029087	28	204,764.02
2158124	29	196,511.67
87041052	30	191,194.79
20012646	31	186,596.08
4268077	32	185,596.40
61588006	33	163,433.92
3748754	34	162,742.35
58876736	36	159,220.27
93201041	36	154,280.00
1792804	38	153,271.99
20019923	38	152,431.58
20029069	39	151,745.48
3748830	41	148,150.91
55024511	41	150,150.22
82073	42	144,111.60
4257025	44	140,066.97
54262248	44	139,271.00
57321990	45	138,385.01
56915036	46	137,192.05
59831955	47	136,053.88
4322443	48	130,930.79
20029102	49	130,540.97
54511359	50	129,189.70
20017534	51	128,149.80
79030248	52	127,513.98
4317166	53	126,994.73
20144705	54	120,775.97
20029072	55	119,838.01

A third issue arises when new columns are introduced to the table, resulting in altered rankings. Furthermore, there are instances of duplicate rankings, which differ from those observed without the extra column. This occurs with the addition of either descriptive columns or additional measures. With each new column or measure incorporated, the rankings shift. The exported data displays rankings that vary from those seen in the table in each case.

Thanks for any help you can offer.

[Anonymous]

Hi @gspollock ,

Here I create a easy sample to have a test. In my side, it could work. [Measure] is a easy sum measure.

According to your statement, I think FACT_SALES[FYTD GP TD] is a measure. Your issue should be caused by this measure. Could you share a sample to show us your data model and your measure logic. 

This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gspollock]

This is the measure used in the rankx:

FYTD GP TD = var yr =
CALCULATE( MAX(DIM_PERIOD_GL_DATE[FISC_YR_NUM]), filter(all(DIM_PERIOD_GL_DATE),DIM_PERIOD_GL_DATE[Date] = TODAY()-1))
var res = CALCULATE([Sales GP AMT], filter(all(DIM_PERIOD_GL_DATE),DIM_PERIOD_GL_DATE[FISC_YR_NUM] = yr ))
return res

This is the Sales GP AMT measure:

Sales GP AMT = SUMX('DIM_OBI_GOV_CUST_SLS_HIER',CALCULATE(SUM(FACT_SLS[EXT_SELL_GP_AMT])))

As I mentioned, another oddity is that when we export we don't get the same results for the Rank Column as shown in the report.

I'm not sure what specifically you are looking for on the data model.  It has 18 tables.  Not all of them affect this particular result.