cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculating composite totals including related tables

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 Maluleka135692       | Sara Maluleka```

Household

```ID |Care Unit ID | Household Name
===+=============+================
1  | 135530      | Maluleka
2  | 135485      | Malepane3  | Unassigned  | Ndlovu```

Beneficiary

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

Bank Transactions

```ID | Description    | Amount  | Care Unit ID
===+================+=========+==============
1  | Groceries      | 150.00  | 135530
2  | School uniform | 500.00  | 1711853  | Rent           | 1000.00 | 1354854  | Creche fees    | 300.00  | 1356924  | 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```

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Calculating composite totals including related tables

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User

## Re: Calculating composite totals including related tables

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

Proud to be a Datanaut!

Frequent Visitor

## Re: Calculating composite totals including related tables

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

Community Support Team

## Re: Calculating composite totals including related tables

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Calculating composite totals including related tables

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