Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

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
TomMartens
Super User
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:

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

 

 

 

 

 



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

View solution in original post

12 REPLIES 12
Ashish_Mathur
Super User
Super User

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/
TomMartens
Super User
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:

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

 

 

 

 

 



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

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 

 

 



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

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:

image.png

Then I adjusted the relationships, please be aware that there is no relationship between the tables "Purchase by Owner" and "Mystery Table":

image.png

 

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

image.png

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
TomMartens
Super User
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.

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

 

2018-11-23_00h46_51.png

 

I hope it makes sense?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.