This topic contains 5 replies, has 2 voices, and was last updated by 
-
Posts
-
I have 3 fact tables (Revenues, Payroll and Expenses) and a Dim table (a Chart of Accounts). All tables have the same columns (period, acc, depart, etc) and a Value column. I intend to consolidate all the values in the Dim table so I can pivot the whole P&L at one single pivot table.
I manage to setup a Calculate to bring Values from tbRevenues and tbPayroll into the calculated column “Values” I created at the Dim table. however, by no means whatsoever neither the Calculate nor the Sum functions bring me the values from tbExpenses. All fact tables have connections to the Dim Table, and it is just not working.
I already tried deleting tbExpenses from the datamodel and connecting once again, but it just doesn’t work. Any insights on why this might be happening?
I created a measure SumVal that sums all values at the Dim table, and when I filter the accounts that should be coming from tbExpenses the result is “blank”.
Thanks in advance,
Hard to say without seeing your data, your model or your measures.
Make sure the relationship is connected to the correct columns.
Also, sometimes what you see is not everything that is there: for example, if the account column in the expenses table has preceding or trailing blanks, you will not see the difference, but preceding or trailing blanks are enough to make a relationship not work.
If you could send a few rows of sample data for the chart of accounts and some related expense records in an Excel workbook, that might help.
Please find attached a sample file of the datamodel.
Now with a small file 🙂
The issue here is data preparation.
In approximately 507 expense records, the 21st character is a dash (“-“), while the “matching” parent record in the COA table the 21st character is a period (“.”).
Of the remaining 54 expense records, at least one (maybe all the rest) had no matching record at all. See factExpenses[KeyPerCen] = 07-17-01-01-01.01-80-2018-12-Scenario1. Since this is a sample data set, it may not be a real issue.
Thank you very much, Tomallan!
It solved the problem I’ve been having. I’ll definetely take more care of data prep in future datamodels!
The forum ‘Power Pivot’ is closed to new topics and replies.