cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RossBI Frequent Visitor
Frequent Visitor

Allocate Amount by calculated ratio in unrelated SCD II table

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. 

 

2018-11-22_05h56_15.png

 

The main transaction table is "Purchase":

 

2018-11-22_06h00_16.png

 

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. 

 

2018-11-22_05h58_54.png

 

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Allocate Amount by calculated ratio in unrelated SCD II table

Hey @RossBI,

 

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:

image.png

 

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 ...

image.png

 

Finally, I created this measure:

Measure = 
SUMX(
    'Purchase'
    ,'Purchase'[Amount] * 

    CALCULATE(SUM('MysteryTable'[Share]))
)

This allows me to create this matrix visual:

image.png

 

Or some other analysis:

image.png

 

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

 

 

 

 

 

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.

Proud to be a Datanaut!

View solution in original post

12 REPLIES 12
Super User
Super User

Re: Allocate Amount by calculated ratio in unrelated SCD II table

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

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.

Proud to be a Datanaut!
RossBI Frequent Visitor
Frequent Visitor

Re: Allocate Amount by calculated ratio in unrelated SCD II table

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.

Super User
Super User

Re: Allocate Amount by calculated ratio in unrelated SCD II table

Hey,

 

can you please add your expected result in the screenshot, given the "sample" data model you provided.

 

Regards,

Tom

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.

Proud to be a Datanaut!
RossBI Frequent Visitor
Frequent Visitor

Re: Allocate Amount by calculated ratio in unrelated SCD II table

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

 

2018-11-23_00h46_51.png

 

I hope it makes sense?

Super User
Super User

Re: Allocate Amount by calculated ratio in unrelated SCD II table

Hey @RossBI,

 

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:

image.png

 

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 ...

image.png

 

Finally, I created this measure:

Measure = 
SUMX(
    'Purchase'
    ,'Purchase'[Amount] * 

    CALCULATE(SUM('MysteryTable'[Share]))
)

This allows me to create this matrix visual:

image.png

 

Or some other analysis:

image.png

 

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

 

 

 

 

 

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.

Proud to be a Datanaut!

View solution in original post

Super User
Super User

Re: Allocate Amount by calculated ratio in unrelated SCD II table

Hi,

 

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
RossBI Frequent Visitor
Frequent Visitor

Re: Allocate Amount by calculated ratio in unrelated SCD II table

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 🙂

RossBI Frequent Visitor
Frequent Visitor

Re: Allocate Amount by calculated ratio in unrelated SCD II table

@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.

Super User
Super User

Re: Allocate Amount by calculated ratio in unrelated SCD II table

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 

 

 

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.

Proud to be a Datanaut!

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)