A Mesure to get data from another table

[MLExcel]

Hi everybody,

I know it's probaly very simple but I just don't get it.

My Datamodell has multiple tables
Table A contains Forecasts
Table B contains Actuals

in additon there are a Calendar and a Materiallist tables that relate the two above with each other.

Table A only has values with the first of any month (Jan 1st, feb 1st ,...) as date.
Table B has the actual sales dates. So there the dates are random.

I want to have the sum of the Actuals (table B) in table A.

So in table A there should be a mesure (or a calculated column) displaying the sum of the sales.

Any help would be highly aprreciated.




By the way the reason for this is, that I want to create a mesure that displays the value of table B (the actuals) if there are any OR else the values from table A.
The result in Excel would be a Pivot report where you have forecast values for some months and actuals for the others in the same line.

 

[Matt Allington]

I think you are thinking about this in the wrong way. It sounds like your tables are joined correctly. There is no need to write measures inside other tables. Just create a pivot table and use the date table and material table as desired on rows and columns for your pivot. The measures are then simple.

Sum(tableB[actuals column])
sum(TableA[forecast column])

 

[MLExcel]

Thank you Matt,

indeed when I reread my post now it all seams a bit weird. Yes of course just to get the two sums is easy and done as you say.

What I was trying to do, is find a way to create a mesure, that uses values from table B if there are any for a given timeframe (months) or the values from table A if table B is empty for that period.
the result should be a pivot table with only one row and the months as columns. The values should be actuals (table B) for the past months and FCST for the other months.



In the meantime I solved this by creating a pivot table with two rows (ACT and FCST) and creating separate normal static table with one row that looksup the values in the pivot.

BR