I'm still fairly new to DAX and I'm struggling with solving an allocation problem. Briefly, what I'm trying to accomplish is similar to this:
Imagine a household budget where you want to allocate the expenses by family members by proportion of total income. Example:
Utility Bill: $100 TotalIncome = $10000. Janet earns $8000 and Joe $2000
Janet pays $80 ($100 * ($8000/TotalIncome)) and Joe pays $20
Below is the example data model.
The main transaction table is "Purchase":
I would like to allocate Amount for each line in purchase to the owners based on proportion of shares. OwnerShares contains the number of shares and is stored as a slowly changing dimension (SCD Type II) as they may buy/sell shares. So it's important the value is calculated by the correct ratio as of the posting date.
I have tried 3 approaches based on similar questions without luck so I'm not picky on how to solve this and feel free to suggest alternative modeling if that can simplify the DAX. What is important is that I somehow can visualize the allocated expenses by owners over time.
I'm wondering if there is a column missing in the owner shares table, that reflects what kind of asset is associated with the shares.
Or can we assume that the assets purchased at a certain time are automatically "owned" by the people who have a share during that time?
I'm also wondering what you want to what the expected result should look like, e.g. do you want a table that contains date/owner/asset/share or just a measure that allocates all the purchases to owner shares table w/o the need for a split of the purchased asset?
Hamburg - Germany If I answer your question, please mark my post as solution, this will also help others. I accept Kudos :-), If you find my post helpful.
There is no direct relation between the type of assets and ownershares. It's not assets in the real business case but I called it that to simplify. The easiest way of explaning it is imagine you buy a TV (asset) as a couple and you want to allocate the cost based on the income ratio.
In the real business case, there are expenses mapped to a given person and then there are "shared" costs (which is the part I'm struggling with). In my example all entries are "shared" and should be distributed by owners according to shares at the posting date.