Using correlation to predict/estimate missing values

Question

I'm struggling to get estimate missing numbers based on a pair of actual figuresTable1 is a complete informationRanking&nbsp;Sales&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 82,967.4146&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 44,405.9852&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 42,306.70103&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32,884.98111&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 31,854.20123&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,021.46134&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 28,735.09160&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,503.01169&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 24,626.69185&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23,213.10192&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,175.79208&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,877.77214&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,370.58248&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17,722.25299&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,091.64300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,918.88307&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,153.49308&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,121.66324&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,089.06361&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8,369.43365&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8,175.49408&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,769.83409&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,599.31411&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,520.38432&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,439.53468&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,802.01&nbsp;And Table2:Ranking&nbsp;Sales&nbsp;1&nbsp;2&nbsp;4&nbsp;5&nbsp;6&nbsp;7&nbsp;8&nbsp;9&nbsp;10&nbsp;11&nbsp;12&nbsp;13&nbsp;14&nbsp;15&nbsp;16&nbsp;17&nbsp;18&nbsp;19&nbsp;20&nbsp;21&nbsp;22&nbsp;23&nbsp;24&nbsp;25&nbsp;26&nbsp;27&nbsp;28&nbsp;29&nbsp;30&nbsp;31&nbsp;32&nbsp;33&nbsp;34&nbsp;35&nbsp;36&nbsp;37&nbsp;38&nbsp;39&nbsp;40&nbsp;41&nbsp;42&nbsp;43&nbsp;44&nbsp;45&nbsp;47&nbsp;48&nbsp;49&nbsp;50&nbsp;51&nbsp;53&nbsp;54&nbsp;55&nbsp;56&nbsp;57&nbsp;58&nbsp;59&nbsp;60&nbsp;61&nbsp;62&nbsp;63&nbsp;64&nbsp;65&nbsp;66&nbsp;67&nbsp;68&nbsp;69&nbsp;70&nbsp;&nbsp;Is there any way I can calculate the correlation in Table 1 and predict all sales in Table 2?The ranking is just purely based on the sales.Thanks!

anonymous · Answer

HI&nbsp;Jay_Lu,
I suppose the ranking formulas are calculated based on the detail level records, when you use it on table visual these records have been aggregated the sales values with the current category.&nbsp;
For this scenario, I think you may need to change the formula to ranking records based on group. You can take a look at the following blog if helps:
RANKX on multiple columns with DAX and Power BI - SQLBI
Regards,
Xiaoxin Sheng

jay_lu · Answer

Hi Xiaoxin,&nbsp;Thanks for your suggestion, but my case would be different.&nbsp;So the ranking is actually from an extenral source, and I lookupvalue the sales via the person's name.&nbsp;By saying that, we know all people's ranking, and some salespersons' sales firgues, and now we want to estimate those missing sales with confirmed rankings.&nbsp;I initially thought to calculate the correlation between known rankings and turnover, and then use a model to predict the missing values. But just don't know how to. maybe there is other way

Forum Discussion

Using correlation to predict/estimate missing values

2 Replies

Related content

Correlation matrix

missing data

missing data

Generating Missing Data

Inferring Missing Columns and Imputing Missing Data

Ranking	Sales
3	82,967.41
46	44,405.98
52	42,306.70
103	32,884.98
111	31,854.20
123	30,021.46
134	28,735.09
160	25,503.01
169	24,626.69
185	23,213.10
192	22,175.79
208	20,877.77
214	20,370.58
248	17,722.25
299	13,091.64
300	12,918.88
307	12,153.49
308	12,121.66
324	11,089.06
361	8,369.43
365	8,175.49
408	5,769.83
409	5,599.31
411	5,520.38
432	4,439.53
468	2,802.01