Report: Sales and forecast

[RasmusRymark]

Hi all

I'm trying to create a report containing sales and forecast (and index calculation based on these). My goal is a report with a product-hierarchy with the actual sales in one column - and the forecast in the nex column. My issue is that the forecast isn't made on the each individual product - but only on product Groups. My input data is illustrated below.

Does anyone have an idea on how I can merge these into one report? Since VA doesn't allow join 2 datasets directly in the report (as far as I know) then I think I need to join the tables on ProductCategory (obviously). But how can I show the FC only on the ProductCategory when drilling in the AC's in the report? When I join the below tables I get a FC of 40 on both Product 1+2+3 = 120. I could maybe just create a Measure as average - but the FC will still be 40 on each product. But 40 on top of the category.

I hope someone can help. AND understand my issue

/Rasmus

ProductID	ProductCategory	Actuals
1	Fruits	10
2	Fruits	20
3	Fruits	20
4	Vegetables	10
5	Vegetables	50

ProductCategory	Forecast
Fruits	40
Vegetables	100

[PeterWijers]

Hi Rasmus,

I solved this issue once appending the data tables.

set the forecast value to 0 for the actuals and set prodID to blank/zero.

set the actual value to 0 for the forecast.

append the data allowing you to report both situations and combined on category level.

but all of this outside of va.

greetings Peter