# This is basic. What am I doing wrong!!



## tbone56 (Jul 20, 2014)

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)

*Product Code**Price**Discount**Country**Link*A1$10.34$9.31AUA1AUA2$12.76$11.48AUA2AUA3$18.79$16.91AUA3AUB1$6.98$6.28AUB1AUB2$5.43$4.89AUB2AUB3$8.34$7.51AUB3AUB4$7.83$7.05AUB4AUA1$11.06$9.96NZA1NZA2$13.65$12.29NZA2NZA3$20.11$18.09NZA3NZB1$7.47$6.72NZB1NZB2$5.81$5.23NZB2NZB3$8.92$8.03NZB3NZB4$8.38$7.54NZB4NZ

<tbody>

</tbody>

*Sales Table* 
The Link Column is a calculated field ie CONCATENATE(Product Code,Country)

*Product Code**Qty**Country**Link**Value*A225AUA2AU#errorB118AUB1AUsee belowB167AUB1AUB147NZB1NZB143NZB1NZB457NZB4NZB244AUB2AUA366NZA3NZA231NZA2NZB47AUB4AU

<tbody>

</tbody> 
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 (Jul 20, 2014)

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 2010ABCDEF1Product CodeQtyCountryLinkLinked_priceValue2A225AUA2AU$ 12.76$ 319.003B118AUB1AU$ 6.98$ 125.644B167AUB1AU$ 6.98$ 467.665B147NZB1NZ$ 7.47$ 351.096B143NZB1NZ$ 7.47$ 321.217B457NZB4NZ$ 8.38$ 477.668B244AUB2AU$ 5.43$ 238.929A366NZA3NZ$ 20.11$ 1,327.2610A231NZA2NZ$ 13.65$ 423.1511B47AUB4AU$ 7.83$ 54.81Sales


----------



## scottsen (Jul 21, 2014)

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 (Jul 21, 2014)

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


----------



## cyrilbrd (Jul 21, 2014)

*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 (Jul 21, 2014)

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 (Jul 21, 2014)

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 (Jul 21, 2014)

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 (Jul 21, 2014)

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.


----------

