Create a column which is the division of an existing column by an existing measure

[Anonymous]

I have a calendar table with two columns of decimal numbers: Column1 and Column2. I also have a measure (Measure1) based on Column2, which is nothing but a moving average of Column2.

I would like to create Column3 as the result of dividing Column1 by Measure1. Is that feasible at all? Does it make it sense?

I can't make it work. I get meaningless numbers if do Column 3 = Column1 / Measure1

[v-lili6-msft]

hi,@ric

    After my research, you can use this measure

Measure = CALCULATE(SUM('Calendar'[Column1]))-[Measure1]

 and drag it into the line chart.

by the way, DATESINPERIOD Function is time intelligence functions, so you need a date table for it.

here is pbix,please try it.

https://www.dropbox.com/s/2qp02y2u86oi751/Create%20a%20column%20which%20is%20the%20division%20of%20a...

Best Regards,

Lin

[v-lili6-msft]

hi,@ric

      We can create a calculated column to reference a measure value like this: Column1= <measure name>. But you need to note the calculated column values are calculated based on table context so it's fixed. So, you need to convert the formula of measure to the formula of column, then add the Column3.

of course, to do the further calculation, you can use measure directly without creating additional calculated column. If you have some specific requirement, please share the sample data and expected results for our analysis.

Best Regards,

Lin

[Anonymous]

See the sample data below. The name of the table is Calendar. It includes both columns: Column1 and Column2.

The measure Measure1 is a moving average of Column2, as you can see below.

My goal is to plot the ratio of Column1 over Measure1 on a line chart with daily granularity. I can't make it work. I don't know if I need a measure or a column to work this out.

Any help/advice will be highly appreciated!

Measure1 = 
CALCULATE (
    SUM ( 'Calendar'[Column2] ),
    DATESINPERIOD ('Calendar'[Date], LASTDATE('Calendar'[Date]),-20,DAY)
) + 0

Date	Column1	Column2
01 January 2012	14.83	1.01
02 January 2012	14.47	0.82
03 January 2012	14.26	0.52
04 January 2012	14.62	1.23
05 January 2012	14.43	0.75
06 January 2012	13.87	0.42
07 January 2012	13.66	1.12
08 January 2012	13.75	0.99
09 January 2012	13.84	1.12
10 January 2012	14.02	0.44
11 January 2012	13.96	1.14
12 January 2012	14.15	0.88
13 January 2012	14.73	1.24
14 January 2012	14.01	0.72
15 January 2012	13.71	0.89
16 January 2012	13.51	0.65
17 January 2012	13.62	1.41
18 January 2012	13.46	0.75
19 January 2012	13.45	0.85
20 January 2012	14.18	1.23
21 January 2012	13.91	0.76
22 January 2012	13.51	0.81
23 January 2012	14.00	1.35
24 January 2012	14.16	0.98
25 January 2012	13.94	0.84
26 January 2012	14.04	0.52
27 January 2012	14.22	1.47
28 January 2012	13.69	0.66
29 January 2012	13.66	0.82
30 January 2012	13.32	0.63
31 January 2012	13.07	0.99
01 February 2012	13.11	0.81
02 February 2012	13.15	0.98
03 February 2012	13.18	1.04
04 February 2012	13.17	0.81
05 February 2012	13.42	1.11
06 February 2012	13.24	0.75
07 February 2012	13.43	1.30
08 February 2012	14.19	1.11
09 February 2012	14.55	1.05
10 February 2012	14.40	0.57
11 February 2012	13.93	1.09
12 February 2012	13.68	0.86
13 February 2012	13.74	1.02
14 February 2012	13.75	0.81
15 February 2012	13.92	1.22
16 February 2012	14.14	0.87
17 February 2012	14.00	0.95
18 February 2012	14.27	1.46
19 February 2012	14.27	1.08
20 February 2012	14.07	0.82
21 February 2012	14.09	0.88
22 February 2012	13.17	0.28
23 February 2012	13.48	1.07
24 February 2012	13.68	1.10
25 February 2012	13.40	1.60
26 February 2012	13.81	1.32
27 February 2012	13.42	0.50
28 February 2012	13.50	1.00
29 February 2012	13.47	0.51
01 March 2012	13.46	0.72
02 March 2012	13.54	1.63
03 March 2012	13.52	1.00
04 March 2012	13.00	0.72
05 March 2012	12.75	0.74
06 March 2012	12.93	0.89
07 March 2012	12.95	0.66
08 March 2012	13.19	1.60
09 March 2012	13.61	0.90
10 March 2012	13.49	1.03
11 March 2012	13.54	0.82
12 March 2012	13.31	0.87
13 March 2012	13.75	1.04
14 March 2012	13.01	0.45
15 March 2012	12.84	0.92
16 March 2012	13.00	1.19
17 March 2012	13.22	1.11
18 March 2012	13.20	0.62
19 March 2012	12.77	0.95
20 March 2012	13.40	1.26
21 March 2012	12.80	0.70
22 March 2012	12.63	0.90
23 March 2012	12.85	1.13
24 March 2012	12.98	1.11
25 March 2012	13.68	1.12
26 March 2012	14.09	1.06
27 March 2012	14.32	1.06
28 March 2012	13.54	0.59
29 March 2012	13.67	1.24
30 March 2012	14.12	0.96
31 March 2012	13.86	0.75
01 April 2012	13.78	0.83
02 April 2012	13.88	0.89
03 April 2012	13.70	0.79
04 April 2012	13.49	0.68
05 April 2012	13.68	0.99
06 April 2012	13.30	0.88
07 April 2012	13.31	1.09
08 April 2012	13.40	0.95
09 April 2012	13.72	1.12
10 April 2012	13.72	0.76
11 April 2012	13.09	0.50
12 April 2012	13.63	1.01
13 April 2012	13.55	0.65
14 April 2012	12.49	0.45
15 April 2012	12.59	0.83
16 April 2012	12.50	1.00
17 April 2012	12.85	1.18
18 April 2012	12.20	0.18
19 April 2012	11.82	0.91
20 April 2012	12.02	1.17
21 April 2012	12.16	1.02
22 April 2012	12.66	0.85
23 April 2012	12.41	0.88
24 April 2012	12.69	1.02
25 April 2012	12.51	1.00
26 April 2012	12.55	0.67
27 April 2012	12.61	0.93
28 April 2012	12.11	0.68
29 April 2012	12.36	0.92
30 April 2012	12.62	0.93
01 May 2012	12.73	1.11
02 May 2012	12.08	0.47
03 May 2012	12.57	1.33
04 May 2012	12.34	0.78
05 May 2012	12.40	0.90
06 May 2012	12.11	0.69
07 May 2012	12.26	1.02
08 May 2012	11.93	0.44
09 May 2012	11.99	0.80
10 May 2012	11.96	0.76
11 May 2012	11.81	0.64
12 May 2012	12.43	1.24
13 May 2012	11.96	0.56
14 May 2012	12.09	0.98
15 May 2012	11.57	0.65
16 May 2012	11.79	0.70
17 May 2012	11.83	1.08
18 May 2012	11.23	0.66
19 May 2012	11.51	1.07
20 May 2012	11.41	0.66
21 May 2012	11.62	0.81
22 May 2012	11.68	1.11
23 May 2012	12.04	1.01
24 May 2012	11.82	0.45
25 May 2012	12.11	1.15
26 May 2012	11.41	0.62
27 May 2012	11.39	0.72
28 May 2012	11.74	0.90
29 May 2012	11.59	0.61
30 May 2012	11.65	1.22
31 May 2012	11.51	0.72
01 June 2012	11.99	0.78
02 June 2012	11.84	0.93
03 June 2012	12.11	0.86
04 June 2012	12.12	0.79
05 June 2012	12.58	1.04
06 June 2012	12.25	0.77
07 June 2012	12.04	0.48
08 June 2012	12.08	0.89
09 June 2012	12.66	1.22
10 June 2012	12.25	0.74
11 June 2012	12.14	0.80
12 June 2012	12.28	0.94
13 June 2012	11.99	0.64
14 June 2012	12.12	1.07
15 June 2012	12.58	1.35
16 June 2012	12.28	0.99
17 June 2012	12.11	0.47
18 June 2012	12.80	0.95
19 June 2012	13.11	1.03
20 June 2012	13.40	1.01
21 June 2012	13.39	0.73
22 June 2012	13.80	1.15
23 June 2012	13.16	0.53
24 June 2012	12.80	0.73
25 June 2012	13.25	1.16
26 June 2012	12.78	0.46
27 June 2012	12.28	0.83
28 June 2012	12.09	0.81
29 June 2012	12.55	1.33
30 June 2012	12.73	0.95
01 July 2012	13.18	0.63
02 July 2012	12.83	0.45
03 July 2012	12.63	0.91
04 July 2012	12.84	1.17
05 July 2012	12.56	0.58
06 July 2012	12.25	0.69
07 July 2012	12.00	0.84
08 July 2012	12.58	0.79