Which videos can help me build an efficient model that distributes costs over business segments using different cost allocation factors to summarize profitability by business segment?
To me is sounds like a combination of many things, so hard to say exactly.
I would highly recommend going through this particular course first if you haven’t already.
Also check out a few of these videos. Some allocation factors around budgeting that could have some relevance
Also here’s another tutorial that I think may be relevant
Hopefully these can all be helpful for what you need.
Hi,
Did you find a good solution on this topic ?
I have been through the training videos on budgetting, it didnt help me either.
I have tried to solve this, but not in a perfect way, so I am still on the search for a good and better solution.
I did a calculated table of only the cost to be allocated to different segments (part of Fact table, repeated for each segment)
I did a new table of the different allocation keys.
I looked up the allocation keys in the Calculated table and did allocation calculations.
My struggle began when trying to create relationship between my calculated table to models existing dim tables, here I got circular references. I have semi-solved not having any relationships, but instead measures created with Intersections to all dimension tables needed, where I could not do relationship.
I would appriciate your feedback on a better way to setup allocation of cost to different business segments.
I too am in search of good solution.
I ended up with kludgy tables created with a lot of lookups.
The segment values I wanted were derived from base $ * allocation %.
I had a definition table which identified which base of $ used which allocation factors.
I created separate tables for the allocation factors.
In a data table, I populated the base, looked up the allocation factor for that base, pulled the % value for that allocation, pulled the for that base, and multiplied the two ( * %) to create the segment value.
This was truly a very messy model that I have yet to clean up.
To assist here would need to see a demo model with the scenario of what you are dealing with.
The more specific you can be with your requirement, the more tailored your answer can be to your problem.
Currently I really have no idea what your are dealing with. It’s too difficult to imagine everything without actually seeing the scenario in a simple form to review and give suggestions.
Thanks
Sam
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
Hi Slee,
How did you create you definition table and could you create relationship to all dimension tables your fact table was connected to ?
With Regards
Martin
Appreciate the info
Just one thing.
Really try to format these. If when looking at this I have no idea what is happening with this formula.
This is also just way to complicated…I rarely if even would need something this complicated.
Anyway this is an example of how you want to format it. I’ve done it here just to attempt to have an idea of what it’s actually looking to calculate.
The same for all these measures.
Hopefully you can appreciate that I or anyone looking at this model is really going to have no idea what happening within these measures.
It’s really important to get into these good habits early on and it help in understand how DAX works in general when doing this.
As I say before as well all of these formula are way to complicated.
I’ll have to look at this for a little bit longer to offer any more suggestions.
It would also be helpful to be a bit more specific on the issue as well, because what you’ve shared here is a reasonably significant model with a lot going on and directing me to exactly where the issue is would help in speeding up a solution.
Thanks
Sam
Ok I’ve done so more reviewing here, and it’s honestly just a bit too much info and is really pushing into consulting which is not really what the forum is all about.
Can we really drill into the formula or table where this is a problem.
Maybe even simplify this down to some simple tables and refined data. Just so we can drill into the specific scenario you’re looking to solve.
At the moment I’m just find it difficult to work out where I’m meant to be looking. There a lot of table, a lot of data, overly complex formulas…so hopefully you can see why I’m a bit confused.
My big suggesting just in general here is to really look to simplify this. It’s just not helpful to yourself attempting to work out what’s happening when your having to work out formulas like this.
It’s almost impossible in my opinion.
I would be relatively confident there is a simple solution to this, but I would like to just see a simplified scenario of it, so that I can get my head around it better.
Thanks
Sam