cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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.

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
12 REPLIES 12
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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.

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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 🙂

Anonymous
Not applicable

@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

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@TomMartens, here's a link to the .pbix including sample data and your answer to my original question.

SharedAllocatePurchaseExample

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

Super User

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

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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

Hey,

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

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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?