Forum Discussion
Advocate IForecast V Actual Comparison Table
Garry,
This is getting more complex with extra columns for Job and Account, and different types of Forecast. I'm not sure what CC and PF and SL and MW are?
From your original model, I agree that linking Account to Forecast seems to make sense. You'll probably need to use a Cross Filter Direction of Single from your Account table to both Forecast and Actual to avoid an 'ambiguous relationship' error. And the same may be required from Jobs as well.
Do you want to analyse Actual vs Forecast by both Job AND Account?
Advocate IAnonymous
Hey steve, the forecast table is made up of Professional Fees (PF), Constructions Costs (CC), etc its for a property development. It will be in one spreadsheet actually but i split it for reporting purposes...The accountantlinkcode will indicate the group.
I am thinking I could merge the FORECAST and ACTUAL table into 1 fact table as advised before...The only unique fields in the forecast is the cashflow description (which i can do without if i can get the account name), transaction date and Value.
Yes there is a requirement for different jobs and account ranges...
I guess i have two fact tables ACCOUNTS from accounting system and FORECAST from Excel which have similar fields.
Given that Actuals (not "ACCOUNTS"?) from the accounting system and Forecast from Excel are imported separately, I'd suggest you start wiith:
- Import Forecast and Actuals as separate fact tables
- Extract out unique Account and Job dimensions, and create your Date dimension table as you've alrady done
- Create relationships from your 3 x dimension lookup tables down to Actuals and Forecast, using "Single" for Cross Filter Direction as needed to avoid ambiguity
- Use the FiscalMonth column on your Date table in measures to join related Actuals with their monthly Forecast
You could collapse it all into just a couple of big table. But doing the above steps up front should give you the foundations of a readable, maintainable model from which to most simply create measures and reports. There's also a good discussion by Matt Allington on creating Lookup/Dimension tables per above at http://exceleratorbi.com.au/create-lookup-table-power-pivot/ thats work a read.
Cheers,
Steve.