Powerpivot model with 2 facttables (?) -
i'm relatively new powerpivot, , have question while working (using excel 2013).
the issue how should model data in powerpivot when have 2 csv files facttables , 1 dimension table.
i have (test)powerpivotmodel sourced 3 .csv files loaded powerpivot.
- a facttable named expenses (holds expenses of person)
- another facttable named expensesdetails holds details of expense-record
- a dimension table named category associated expensedetail table
expenses , expensesdetails have pretty same information
--> 1 expense record 1 record in table expenses , 1 or more records in expensesdetail table
--> exampel 1 record in both tables amount can related 1 category
--> if 1 amount relates 2 (or more) categories, in database there 1 record in expense table , 2 (or more) records in expensedetail table
the category table associated expensedetail table.
when load these 3 tables powerpivot, these relations:
- 1 between expensedetail , category
- 1 between expensedetail , expenses
then
- can make perfect pivot chart when use fields expensedetails , category (they directly linked)
- when use fields both category , expense table incorrect data.
when make sum of expenseamount category, sum same categories...
probably because between 2 tables expensedetails table located (...)
-->additionally, excel indicates mesasage relation misses...
how should fix in datamodel?
thinking making star model 1 facttable... 2 csv files different columns, how can make 1 facttable of it?
if 1 facttable not possible, right way make datamodel?
thanks in advance thoughts and/or support!
roberto
i don't think there wrong model. cannot combine values expense table category table because not directly related. if need able view fields expense , category in same pivot table, use related function bring values expense detail table calculated columns , create measures in expense table there in expense details based off of calculated columns. you'll need use summarize function make measures return appropriate amounts, since these summarized @ expense level rather expense details level.
Comments
Post a Comment