cancel
Showing results for
Did you mean:
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.

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

1 ACCEPTED SOLUTION

Accepted Solutions
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:

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.

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

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
12 REPLIES 12
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
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
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

## 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
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
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

I hope it makes sense?

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:

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.

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

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Super User

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

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
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 🙂

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")`

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

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

Hey,

this is why initially asked for a missing relationship 🙂 😞

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
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Announcements

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

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

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)