Calculating composite totals including related tables

[rudivs]

I am trying to calculate the total expenses that have been spent on particular Households. I have the following tables defined: Household, Beneficiary, Care Unit and Bank Transaction (the fact table), with the basic (simplified) properties and relationships as in the image below, and the following general properties:

• Bank Transactions have a column for Care Unit ID, which can be null. The Care Unit is used to designate who benefited from an expense, and it can be either a Household (for example, paying for groceries that benefit everyone) or it could be a Beneficiary (for example, school fees that benefits one person). There is a 1:many relationship between Care Unit and Bank Transactions.
• Household has a column for Care Unit ID which is never null, but can be "Unassigned" if a Care Unit ID has not been created for it yet. There is a 1:1 bidirectional relationship between Care Unit and Houshold in Power BI.
• Beneficiary has a column for Care Unit ID which can be null. There is a 1:many relationship between Household and Beneficiary. Every Beneficiary belongs to a Household. There is a 1:many inactive relationship between Beneficiary and Care Unit (it won't let me make it 1:1 even though there would only ever be 1 Beneficiary per Care Unit ID and vice versa).

Tables and relationships

I can make a table showing all the expenses that have been allocated directly to a particular Household, but I would like to get a composite total showing the amount allocated to a Household and its Beneficiaries. Is there a recommended way to do this?

Here is some sample data:

Care Unit

Care Unit ID | Care Unit Name
=============+================
135530       | Maluleka
135485       | Malepane
171185       | Tshepo Maluleka
135692       | Sara Maluleka

Household

ID |Care Unit ID | Household Name
===+=============+================
1  | 135530      | Maluleka
2  | 135485      | Malepane
3  | Unassigned  | Ndlovu

Beneficiary

ID |Care Unit ID | Beneficiary Name | Household ID
===+=============+==================+==============
1  | 171185      | Tshepo Maluleka  | 1
2  | 135692      | Sara Maluleka    | 1
2  | Unassigned  | Maria Ndlovu     | 3

 Bank Transactions

ID | Description    | Amount  | Care Unit ID 
===+================+=========+==============
1  | Groceries      | 150.00  | 135530
2  | School uniform | 500.00  | 171185
3  | Rent           | 1000.00 | 135485
4  | Creche fees    | 300.00  | 135692
4  | Transport      | 250.00  | null

I would like to be able to produce the following table in a visualisation:

Household | Total Expenses 
==========+================
Maluleka  | 950.00
Malepane  | 1000.00

At the moment, I can only get to:

Household | Total Expenses 
==========+================
Maluleka  | 150.00
Malepane  | 1000.00

[LivioLanzo]

Hi @rudivs

is it safe to say that the people within the same household belong to the same Care Unit, so there's a 1 to many between Care Unit and Beneficiaries

[rudivs]

@LivioLanzo

There is a direct relationship between Care Unit and Beneficiaries, but also an indirect relationship via households (a Care Unit could link to a Household or a Beneficiary; a Beneficiary can be part of up to two Care Units: one directly, and one via Household). Perhaps Allocation Unit would be a better term to use than Care Unit. I've edited my original question to add some sample data that I hope will make this clearer.

[v-jiascu-msft · Accepted solution]

Hi @rudivs,

Please download the demo in the attachment. I would suggest you share a pbix file next time. 

1. Create a measure. Please be aware of the "unassigned" could be a valid relationship.

Measure =
CALCULATE (
    CALCULATE (
        SUM ( 'Bank Transactions'[Amount] ),
        USERELATIONSHIP ( Beneficiary[Care Unit ID], 'Care Unit'[Care Unit ID] )
    )
        + SUM ( 'Bank Transactions'[Amount] ),
    ISBLANK ( Household[Care Unit ID] ) = FALSE ()
)

Best Regards,
Dale

[rudivs]

Hi Dale,

Thank you, I came to the same solution (for some reason my post was marked as spam, so I couldn't update it). You are right about unassigned. The reason why I could create the 1:1 relationship with Households was that there was only one "unassigned" row. Beneficiaries wouldn't let me do it because there were multiple "unassigned" rows. In the end I used Power Query to update the Care Unit ID in the Household and Beneficiary tables using the ID column to ensure uniqueness:

= Table.ReplaceValue(#"Changed Type",each [Care Unit ID],each if [Care Unit ID] = null then "Unsassigned"&Number.ToText([ID], "D", "") else [Care Unit ID],Replacer.ReplaceValue,{"Care Unit ID"})

That let me create a 1:1 relationship with Households (active), and a 1:1 relationship with Beneficiaries (inactive). Next time I will share a pbix.

Thanks again,

Rudi