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
rudivs
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 relationshipsTables 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

 

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

Calculating-composite-totals-including-related-tables

 

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.

View solution in original post

4 REPLIES 4
LivioLanzo
Solution Sage
Solution Sage

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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

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

Calculating-composite-totals-including-related-tables

 

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.

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

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.