topic Using correlation to predict/estimate missing values in DAX Commands and Tips

Using correlation to predict/estimate missing values

Jay_Lu — Wed, 14 Dec 2022 02:38:21 GMT

I'm struggling to get estimate missing numbers based on a pair of actual figures

Table1 is a complete information

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

And Table2:

Ranking	Sales
1
2
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
47
48
49
50
51
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

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!

Re: Using correlation to predict/estimate missing values

Anonymous — Thu, 15 Dec 2022 01:28:21 GMT

HI 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.

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

Re: Using correlation to predict/estimate missing values

Jay_Lu — Thu, 15 Dec 2022 02:07:26 GMT

Hi Xiaoxin,

Thanks for your suggestion, but my case would be different.

So the ranking is actually from an extenral source, and I lookupvalue the sales via the person's name.

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.

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