Hi,
I am including demo model of my solution on allocating cost from one division to other divisions, where I cannot create a relationship to my dim tables from my allocation table, why I have used intersect functions to combine data before allocation with allocation data.
I am using method of setting of a new calculated table, to handle to calculation, as I am not able to create measures basis Fact table to move costs from one division to another division. I would have preferred to do the allocation as measures on Fact table, for me to re-use all relationship with dim tables. If you know a way to handle cost allocation moving cost from one division to another, I would be very interested.
I have 6 divisions, where part of costs in 2 division need to be allocated to the remaining 4 divisions, basis allocation keys per Year/Cost.group (we misuse market as cost group). I need to see cost per division before and after allocation. Even that my allocation keys are per year and costgroup, I do the actual allocation per month and account, being able to drill down on this level and to reduce work maintaining allocation keys.
I am able to create hardcoded measures for each of the 4 divisions on cost allocated to each division, but only by measure name as cost is still on Fact Table division (the 2 cost pooling divisions), why I cant list cost for all divisions and see costs before and after allocation.
I have therefore created a calculated table as crossjoin from Fact table cost base to be allocated and from dim tables to repeat this cost pool on all 6 divisions, then moving in allocation keys from Allocation key table to calculate cost allocated to each division. I then have one column with divisions and another with allocation cost, but I am not able to create relation ship from this allocation table to dim table, where I have linked my fact table to division and therefore I cannot create a measure combining fact table (before allocation) with Allocation table (allocation data), this is my I have been forced to do measure that intersect with main dimenstions, I need to do this allocation before/after report on.
Fact table is in my demo model named “CognosData”
Dim table that link entities to division is named “BSR”
new table Allocation, is crossjoin table with allocation calculations
new table Allocation Key, hold the allocation keys
I would appreciate you guidance, doing a better setup on this allocation exercise.
Allocation test 12.9.pbix (1.1 MB)
With kind regards
Martin Muller