Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
Solved! Go to 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
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!
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 () )
Best Regards,
Dale
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |