error message not sure what to do

[FCF]

i have this message when  trying to get a value from a column

[lkalawski]

@FCF

If you are creating a measure, you cannot refer to a column this way. The measure returns a single value, so you must use one of the aggregate functions (SUM, MIX, MAX etc.).

If you want to compute something at row level then add a calculated column - then you can do it just like you do now.

_______________
If I helped, please accept the solution and give kudos! 😀

[FCF]

hi

thanks for the response, i am really new to this and am not sure what you mean by creating a calculated column

[lkalawski]

@FCF 

If you have a table and you want to add an additional column that calculates something based on other columns in this table, then you click calculated New Column and you create a formula to calculate.

You can read more about it here: https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

_______________
If I helped, please accept the solution and give kudos! 😀

[FCF]

Hi again

the values that i want to work with come from 2 differnet tables.

i have created the calculated column, but am not able to see one of the columns form a differnt table.

this should not be so confusing.

thanks

[amitchandak]

@FCF , screenshot is not making clear, column, or measure. Both ways it is looking wrong. No need for the table name first. If measure the second one needs to aggeration.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[FCF]

this is an example of the targets.xlxs table

Salesperson	Customer	Wk #	Reference	Target Sales (CDN$)	Target Margin(CDN$)	YR
JIM	VAPLWE	2	JIMVAPLWE2	615	145.38	2020
JIM	VAPLWE	4	JIMVAPLWE4	615	139.79	2020
JIM	VAPLWE	6	JIMVAPLWE6	630	266.2	2020
JIM	VAPLWE	7	JIMVAPLWE7	171	35.51	2020
JIM	VAPLWE	8	JIMVAPLWE8	652	181.59	2020
JIM	VAPLWE	10	JIMVAPLWE10	615	145.38	2020
JIM	VAPLWE	12	JIMVAPLWE12	615	156.58	2020
JIM	VAPLWE	14	JIMVAPLWE14	615	117.4	2020
JIM	VAPLWE	16	JIMVAPLWE16	696	141.73	2020
JIM	VAPLWE	18	JIMVAPLWE18	619	137.25	2020
JIM	VAPLWE	20	JIMVAPLWE20	619	148.45	2020
JIM	VAPLWE	22	JIMVAPLWE22	761	181.84	2020
JIM	VAPLWE	24	JIMVAPLWE24	622	152.02	2020
JIM	VAPLWE	26	JIMVAPLWE26	622	124.04	2020
JIM	VAPLWE	28	JIMVAPLWE28	624	153.74	2020
JIM	VAPLWE	30	JIMVAPLWE30	711	193.03	2020
JIM	VAPLWE	32	JIMVAPLWE32	608	137.87	2020
JIM	VAPLWE	34	JIMVAPLWE34	644	173.63	2020
JIM	VAPLWE	36	JIMVAPLWE36	743	158.37	2020
JIM	VAPLWE	38	JIMVAPLWE38	603	127.63	2020
JIM	VAPLWE	41	JIMVAPLWE41	781	177.06	2020
JIM	VAPLWE	42	JIMVAPLWE42	611	141.11	2020
JIM	VAPLWE	44	JIMVAPLWE44	697	154.79	2020
JIM	VAPLWE	46	JIMVAPLWE46	618	137.18	2020
JIM	VAPLWE	52	JIMVAPLWE52	604	139.52	2020

sales table

Customer	Posting date	Sales (CDN$)	ActualMargin	Salesperson	Week	Year	Sell-to customer no	Key3	act_mar
INPRPI	28/12/2016	31.5	4.5	JIM	52	2016	INPRPI	INPRPIJIM201652	4.5
INPRPI	28/12/2016	31.5	4.5	JIM	52	2016	INPRPI	INPRPIJIM201652	4.5
INPRPI	28/12/2016	31.5	4.5	JIM	52	2016	INPRPI	INPRPIJIM201652	4.5
INPRPI	21/12/2016	31.5	4.5	JIM	51	2016	INPRPI	INPRPIJIM201651	4.5
INPRPI	20/12/2016	31.5	4.5	JIM	51	2016	INPRPI	INPRPIJIM201651	4.5
INPRPI	07/12/2016	31.5	4.5	JIM	49	2016	INPRPI	INPRPIJIM201649	4.5
INPRPI	05/12/2016	31.5	4.5	JIM	49	2016	INPRPI	INPRPIJIM201649	4.5
INPRPI	05/12/2016	31.5	4.5	JIM	49	2016	INPRPI	INPRPIJIM201649	4.5
INPRPI	05/12/2016	31.5	4.5	JIM	49	2016	INPRPI	INPRPIJIM201649	4.5
INPRPI	30/11/2016	31.5	4.5	JIM	48	2016	INPRPI	INPRPIJIM201648	4.5
INPRPI	30/11/2016	31.5	4.5	JIM	48	2016	INPRPI	INPRPIJIM201648	4.5
INPRPI	30/11/2016	31.5	4.5	JIM	48	2016	INPRPI	INPRPIJIM201648	4.5
INPRPI	05/10/2016	31.5	4.5	JIM	40	2016	INPRPI	INPRPIJIM201640	4.5

3rd table

calendar

Date	M-#	Yr	Fiscal	Quarter	M-Name	Wk	Month	WK#	order	ord1	ord2	wk_sort
28/07/19	7	2019	FY20	Q1-20	Jul	31.19	7~Jul	31.19	1.00	1.20	1.200	31.191
29/07/19	7	2019	FY20	Q1-20	Jul	31.19	7~Jul	31.19	1.00	1.20	1.201	31.192
30/07/19	7	2019	FY20	Q1-20	Jul	31.19	7~Jul	31.19	1.00	1.20	1.202	31.193
31/07/19	7	2019	FY20	Q1-20	Jul	31.19	7~Jul	31.19	1.00	1.20	1.203	31.194
01/08/19	8	2019	FY20	Q1-20	Aug	31.19	8~Aug	31.19	1.00	1.20	1.204	31.195
02/08/19	8	2019	FY20	Q1-20	Aug	31.19	8~Aug	31.19	1.00	1.20	1.205	31.196

i have used the calendar to create the relationships 

from the target tabel i want to pull the [Target Margin(CDN$) ] -sales table [act_mar] = [margin_Variance]

i hope this helps.

[v-alq-msft · Accepted solution]

Hi, @FCF 

You may try the following calculated column or measure.

Calculated column:

Column = 
Targets[ Target Margin(CDN$) ]-
CALCULATE(
    SUM(Sales[act_mar]),
    FILTER(
        ALL(Sales),
        [Week]=EARLIER(Targets[Wk #])
    )
)

Measure:

Measure = 
SELECTEDVALUE(Targets[ Target Margin(CDN$) ])-
CALCULATE(
    SUM(Sales[act_mar]),
    FILTER(
        ALL(Sales),
        [Week]=SELECTEDVALUE(Targets[Wk #])
    )
)

Best Regards

Allan

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

[FCF]

Hi

thanks for the help with this problem, i will test the two options.

[FCF]

Hi

I have found a way to make the measure work for me thanks for your help.

[FCF]

good morning

thanks for the response, 

i have tried the measure but the reference to [week] is not working.  i have an error message 

"column week cannot be found or may not be used in this expression."

[FCF]

is it possible to upload the pbx file here?

[lkalawski]

@FCF

Can you provide details of what you want to do, e.g. adding sample data?

To get data from another table, you must use the RELATED function.

https://dax.guide/related/

_______________
If I helped, please accept the solution and give kudos! 😀