Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All
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.
Like:
Desks: Janet 5000 & Joe 5000
Machinery: Janet 6666.66 & Joe 3333.33
Or
2018: Janet 23320 & Joe 16660
Thank you!
I hope someone can
Solved! Go to Solution.
Hey @Anonymous,
I started to create a calculated column "Share" in the OwnerShares table using this DAX statement:
Share = var thisValidFrom = 'OwnerShares'[ValidFrom] var thisValidTo = 'OwnerShares'[ValidTo] var thisShares = 'OwnerShares'[Shares] return thisShares / CALCULATE( SUM('OwnerShares'[Shares]) ,FILTER( ALL('OwnerShares') ,'OwnerShares'[ValidFrom] = thisValidFrom && 'OwnerShares'[ValidTo] = thisValidTo ) )
Then I created a calculated table "MysteryTable" that spreads the share of each owner for each day given by the timeframe (ValidFrom - ValidTo) using this DAX statement:
MysteryTable = GENERATE( 'OwnerShares' ,DATESBETWEEN( 'Calendar'[Date] ,'OwnerShares'[ValidFrom] ,'OwnerShares'[ValidTo] ) )
The next screenshot shows a fraction of this table:
The number of rows is limited to max date of the calendar table, this means that even the ValidTo column contains the year 9999, the spread will be limited, personally, I think this makes sense.
I added some relationships ...
Finally, I created this measure:
Measure = SUMX( 'Purchase' ,'Purchase'[Amount] * CALCULATE(SUM('MysteryTable'[Share])) )
This allows me to create this matrix visual:
Or some other analysis:
As you can see, it's not necessary to use the "MysteryTable" in the matrix visual, this table can also be hidden in the report view.
Hopefully, this is what you are looking for.
Regards,
Tom
Hi,
Share the link from where i can download your PBI file.
Hey @Anonymous,
I started to create a calculated column "Share" in the OwnerShares table using this DAX statement:
Share = var thisValidFrom = 'OwnerShares'[ValidFrom] var thisValidTo = 'OwnerShares'[ValidTo] var thisShares = 'OwnerShares'[Shares] return thisShares / CALCULATE( SUM('OwnerShares'[Shares]) ,FILTER( ALL('OwnerShares') ,'OwnerShares'[ValidFrom] = thisValidFrom && 'OwnerShares'[ValidTo] = thisValidTo ) )
Then I created a calculated table "MysteryTable" that spreads the share of each owner for each day given by the timeframe (ValidFrom - ValidTo) using this DAX statement:
MysteryTable = GENERATE( 'OwnerShares' ,DATESBETWEEN( 'Calendar'[Date] ,'OwnerShares'[ValidFrom] ,'OwnerShares'[ValidTo] ) )
The next screenshot shows a fraction of this table:
The number of rows is limited to max date of the calendar table, this means that even the ValidTo column contains the year 9999, the spread will be limited, personally, I think this makes sense.
I added some relationships ...
Finally, I created this measure:
Measure = SUMX( 'Purchase' ,'Purchase'[Amount] * CALCULATE(SUM('MysteryTable'[Share])) )
This allows me to create this matrix visual:
Or some other analysis:
As you can see, it's not necessary to use the "MysteryTable" in the matrix visual, this table can also be hidden in the report view.
Hopefully, this is what you are looking for.
Regards,
Tom
Thank you so much @TomMartens!
It worked like a charm. As always, the goal post is moving and won't be long I have to figure out how to combine the shared here with direct allocations. But I'll try myself first 🙂
@TomMartens, I'm sorry for bugging you again. I seem to fail extending your solution to also include specific allocations i.e. asset only has one liable owner.
Imagine the Purchase table has a "PurchaseOwner" column with values: Shared, Joe & Janet.
Let's say Joe buys a TV for $1000 in 2018-02-01 (50/50 ratio period). Then I would expect Joe's total would be $11000 and Janet $10000 for that month
I isolated your measure to only include "Shared" by first finding total shared:
TotalShared = CALCULATE(SUM(Purchase[Amount]),Purchase[PurchaseOwner]="Shared")
And then adjusting your answer:
AllocAmount = SUMX( 'Purchase' ,[TotalShared] * CALCULATE(SUM('MysteryTable'[Share])) )
However, I can't figure out how to combine above with the assets that are not shared. I regret I thought this part was so easy I didn't even bother to include it in my original example.
Hey,
this is why initially asked for a missing relationship 🙂 😞
Please prepare sample data that reflects your "real" world scenario, upload your pbix and or xlsx to onedrive or dropbox, and share the link.
I would solve it this way:
Create an additional table that has these columns:
purchased by | Owner
shared | Janet
shared | Joe
Janet | Janet
Joe | Joe
Now, use these values in addition to "build" the content of the "mystery table".
Regards,
Tom
@TomMartens, here's a link to the .pbix including sample data and your answer to my original question.
Thanks!
Hello,
How could I get the exsample file?
Because the message in Danish -"The link was set to expire after a certain period of time. Contact the person who shared this link with you."
Hey,
due to the "added" dimensionality of the purchases, things change 🙂
In your pbix file I created a table called "Purchase Spread" using the following DAX statement (of course this could also be done using Power Query or already in a DWH):
Purchase Spread = UNION( ROW("PurchaseOwner", "shared", "Owner", "Joe") ,ROW("PurchaseOwner", "shared", "Owner", "Janet") ,ROW("PurchaseOwner", "Joe", "Owner", "Joe") ,ROW("PurchaseOwner", "Janet", "Owner", "Janet") )
Now that the purchases are not any longer shared in general I also spread the original Purchase table to incorporate the Owner (PurchaseName from the Purchase Spread table), once again, this could also be done in prior steps or different data storage layers. I used this DAX statement:
Purchase by Owner = GENERATE( 'Purchase', SELECTCOLUMNS( Filter('Purchase Spread','Purchase Spread'[PurchaseOwner] = 'Purchase'[PurchaseOwner]) ,"Owner", 'Purchase Spread'[Owner] ) )
Basically, it's an inner join between the tables "Purchase" and "Purchase Spread"
After this, the "Purchase by Owner" table looks like this:
Then I adjusted the relationships, please be aware that there is no relationship between the tables "Purchase by Owner" and "Mystery Table":
The table "Purchase Spread" can be considered an intermediate table, that is just used to create the table "Purchase by Owner", and for this reason can also be hidden in the report view.
Finally, I created a new measure in the table "Purchase by Owner":
Allocation Amount = SUMX( 'Purchase by Owner' ,IF( 'Purchase by Owner'[PurchaseOwner] = "shared" , var thisOwner ='Purchase by Owner'[Owner] var thisPurchaseDate = 'Purchase by Owner'[Date] var share = CALCULATE( MAX('MysteryTable'[Share]) ,'MysteryTable'[Owner] = thisOwner ,'MysteryTable'[Date] = thisPurchaseDate ) return 'Purchase by Owner'[Amount] * share ,'Purchase by Owner'[Amount] ) )
Here is the report - please be aware that the table "Purchase by Owner is used":
Regards,
Tom
Hey,
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?
Regards,
Tom
Hi @TomMartens, thank you for replying!
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.
The end goal is a measure that allocates.
Any help would be appreciated.
Hey,
can you please add your expected result in the screenshot, given the "sample" data model you provided.
Regards,
Tom
Intended result in a matrix visual:
Rows: Year-Month (Dates)
Columns: Name (Persons) or Owner (OwnerShares)... whatever makes most sense
Values: The measure I'm trying to create
I hope it makes sense?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |