This is basic. What am I doing wrong!!

[tbone56]

In Power Pivot I have imported 2 simple (excel) table (see below).
In each table I created (within Power Pivot) a calculated column "Link" to provide a unique key.

Theses links are used in a relationship

Price Table
The Link Column is a calculated field ie CONCATENATE(Product Code,Country)
[TABLE="width: 0"]
<tbody>[TR]
[TD]Product Code[/TD]
[TD]Price[/TD]
[TD]Discount[/TD]
[TD]Country[/TD]
[TD]Link[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]$10.34[/TD]
[TD]$9.31[/TD]
[TD]AU[/TD]
[TD]A1AU[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]$12.76[/TD]
[TD]$11.48[/TD]
[TD]AU[/TD]
[TD]A2AU[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]$18.79[/TD]
[TD]$16.91[/TD]
[TD]AU[/TD]
[TD]A3AU[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]$6.98[/TD]
[TD]$6.28[/TD]
[TD]AU[/TD]
[TD]B1AU[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]$5.43[/TD]
[TD]$4.89[/TD]
[TD]AU[/TD]
[TD]B2AU[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[TD]$8.34[/TD]
[TD]$7.51[/TD]
[TD]AU[/TD]
[TD]B3AU[/TD]
[/TR]
[TR]
[TD]B4[/TD]
[TD]$7.83[/TD]
[TD]$7.05[/TD]
[TD]AU[/TD]
[TD]B4AU[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]$11.06[/TD]
[TD]$9.96[/TD]
[TD]NZ[/TD]
[TD]A1NZ[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]$13.65[/TD]
[TD]$12.29[/TD]
[TD]NZ[/TD]
[TD]A2NZ[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]$20.11[/TD]
[TD]$18.09[/TD]
[TD]NZ[/TD]
[TD]A3NZ[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]$7.47[/TD]
[TD]$6.72[/TD]
[TD]NZ[/TD]
[TD]B1NZ[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]$5.81[/TD]
[TD]$5.23[/TD]
[TD]NZ[/TD]
[TD]B2NZ[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[TD]$8.92[/TD]
[TD]$8.03[/TD]
[TD]NZ[/TD]
[TD]B3NZ[/TD]
[/TR]
[TR]
[TD]B4[/TD]
[TD]$8.38[/TD]
[TD]$7.54[/TD]
[TD]NZ[/TD]
[TD]B4NZ[/TD]
[/TR]
</tbody>[/TABLE]


Sales Table
The Link Column is a calculated field ie CONCATENATE(Product Code,Country)
[TABLE="width: 0"]
<tbody>[TR]
[TD]Product Code[/TD]
[TD]Qty[/TD]
[TD]Country[/TD]
[TD]Link[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]25[/TD]
[TD]AU[/TD]
[TD]A2AU[/TD]
[TD]#error[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]18[/TD]
[TD]AU[/TD]
[TD]B1AU[/TD]
[TD]see below[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]67[/TD]
[TD]AU[/TD]
[TD]B1AU[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]47[/TD]
[TD]NZ[/TD]
[TD]B1NZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]43[/TD]
[TD]NZ[/TD]
[TD]B1NZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B4[/TD]
[TD]57[/TD]
[TD]NZ[/TD]
[TD]B4NZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]44[/TD]
[TD]AU[/TD]
[TD]B2AU[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]66[/TD]
[TD]NZ[/TD]
[TD]A3NZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]31[/TD]
[TD]NZ[/TD]
[TD]A2NZ[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B4[/TD]
[TD]7[/TD]
[TD]AU[/TD]
[TD]B4AU[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Next I created a relationship for the 2 “Link” columns. Sorry I cannot paste the screenshot

I now want to use the relationship. The Value column in the Sales table is a simple calculated field ie =[Qty]*Price[Price] which should produce the “Value” of the sale. But I get the following error in every row

"The value for column 'Price' in table 'Price' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified."


What am I doing wrong!

 

[cyrilbrd]

Try as follows:
In Sales_tbl add the CalculatedColumn with the following formula:=LOOKUPVALUE(Price_tbl[Cal_price],Price_tbl[Link],[Link]) rename it 'Linked_price'
Then add another Column 'Value' with the formula =[Qty]*[Linked_price]
Would that work for you?

Excel 2010
	A	B	C	D	E	F
1	Product Code	Qty	Country	Link	Linked_price	Value
2	A2	25	AU	A2AU	$ 12.76	$ 319.00
3	B1	18	AU	B1AU	$ 6.98	$ 125.64
4	B1	67	AU	B1AU	$ 6.98	$ 467.66
5	B1	47	NZ	B1NZ	$ 7.47	$ 351.09
6	B1	43	NZ	B1NZ	$ 7.47	$ 321.21
7	B4	57	NZ	B4NZ	$ 8.38	$ 477.66
8	B2	44	AU	B2AU	$ 5.43	$ 238.92
9	A3	66	NZ	A3NZ	$ 20.11	$ 1,327.26
10	A2	31	NZ	A2NZ	$ 13.65	$ 423.15
11	B4	7	AU	B4AU	$ 7.83	$ 54.81
Sales

 

[scottsen]

When you create a calculated column it has a "row context". The calculation is done 1 row at a time, and can thus reference things like Price[Price].

For a measure (calculated field)... you are talking about things "in aggregate". Many rows at once. If you were writing a "Total Sales" measure, you would write =SUM(Price[Price]) and be happy. SUM() is used to aggregate multiple rows together.

Your measure has =[Qty] but look at B1NZ... you have 2 of them!

You probably have two choices here:
1) add a calc column for price*qty (as cyrilbrd suggests, though copying the price over isnt needed, you can just use RELATED())
2) write your measure something like =SUMX(Sales, Sales[Qty] * VALUES(MIN(Price[Price]))

SUMX is going to iterate 1 row at a time, so Sales[Qty] with no aggregate is fine. But the price table isn't really part of that row iterator, so it has to have some aggregate function. You could use MIN(), MAX() whatever, in theory... it should not matter. I use VALUES() because if it matches more than 1 row, it will blow up, which sounds good to me. (better than having no idea something weird happened...)

 

[cyrilbrd]

Thanks for the reminder scottsen... RELATED was indeed what I was looking for (still reading Rob's book...)

 

[cyrilbrd]

scottsen Would =RELATED(Price_tbl[Price])*[Qty] in the table Sales be considered efficient? Or it is better to use a column with related only and a column with sumx?

 

[scottsen]

That's a question for the ages, cyrilbrd.

If you are doing this on a small table... say 100k rows or less, i wouldn't even spend brain cycles on this. Do the calc column multiplication and move on.

More than that, I would probably do it in the database (assuming that was my source data). While I would still have an extra column... at least it would be compressed. (Calc columns are generally NOT compressed).

If we started getting over 1 million rows, you are well into "test it" land. It may be that the overhead of extra space taken up by the column... is worse than the extra calculation taken up by the SUMX. But there are no hard and fast rules there.

 

[cyrilbrd]

Mmmh, yes I can imagine that, volume of data would definitely call for different approaches... Noted re SUMX, will look into that further.


Thanks gain Scottsen, looking forward to read your next intervention on PowerPivotPro...

 

[tbone56]

Thanks scottsen and cyrilbrd, that helped enormously. I now realise that I need to understand DAX functions as well as I understand Excel functions.

scottsen forgive me but I didnt follow the SUMX and VALUE part as the price table has unique "link" vales even though Sales links are not. I found that by using =RELATED('Price'[Price])*[Qty]) in the Sales table did the job. Is this wrong? Error prone?

 

[scottsen]

I think that is likely the best way to solve it

I wrote lots of text trying to describe the difference between a measure (calculated field) and a calculated column... because based on your error... it looked like you were trying to write the former.

But your answer here is clearly a calc column, and a good way to solve it.