Forum Discussion
Modelling XML IATI data in PowerBI - multiple categories, apportioned expenditure
Hi everyone,
I'm hoping to get some advice on a data model I am attempting to build using International Aid Transparency Data. The IATI standard is an XML formatted, nested structure. What I'm trying to do is figure out the best way to model this nested relationship in Power BI, so that I can slice and dice easily and accurately.
This is a simplified version of what I will end up with (the entire IATI XML structure is below if you're interested - I'm just trying to chip off the parts that are of most interest first). The main issue I'm having is being able to calculate total expenditure (based on transactions), and apportion that correctly by Country and Sector (or a combination of the two).
Activity (ID title, description, start/end dates etc)
*Country (country name, apportionment percentage)
*Sector (sector name, apportionment percentage)
*Transactions (amount, type, date)
E.g. you'd end up with something like this for the base tables (extracted from the XML schema)
Activities
| Activity ID | Activity title | Activity description |
| Activity123 | Feeding sharks | Providing food for sharks |
Activity Countries
| Activity ID | Country | Percentage |
| Activity 123 | Tuvalu | 50 |
| Activity 123 | Tonga | 50 |
Activity sectors
| Activity ID | Sector | Percentage |
| Activity 123 | Oceans | 10 |
| Activity 123 | Nutrition | 90 |
Transactions
| Activity ID | Transaction amount | Transaction Type | Transaction date |
| Activity 123 | $100 | Expenditure | 16 March 2021 |
| Activity 123 | $500 | Expenditure | 12 February 2022 |
An example of the full standard is here
Example Usage XML - iatistandard.org
So my issue is: I want to be able to correctly slice and dice using the transaction.amount and the percentage apportionments - e.g. if be able to create visuals which answer questions like "How much spent on Oceans?" or "How much was spent on Oceans in Tonga?" and come out with the correct answer
E.g.
Total expenditure for activity123 = $600
Expenditure on Oceans (10%) = $60
Expenditure in Tonga (50%) = $300
Expenditure in Tonga on Oceans = $30
I'm just at a dead end for how to combine/relate/model the tables to achieve what I need to, without it becoming on giant flat transactions table...
Thanks in advance to all of you expert data modellers. Surely someone has come across this kind of nesting/structure before and can give me some inspriation!
Anonymous , You should merge the last three tables into third one to get % columns, sector, and country.
Using those % you should create a new column with the actual value for country sector combination
2 Replies
Anonymous , You should merge the last three tables into third one to get % columns, sector, and country.
Using those % you should create a new column with the actual value for country sector combination
I think I figured it out, and it's similar to your solution. Thank you!!
I did end up creating [edit: using merge as new] a new table called "Expenditure" with the columns
Activity ID
Country
Country %
Sector
Sector %
And then added a calculated column which summed the transactions for the activity (it uses my internal column names below)
Apportioned expenditure = calculate(sum(activities[Total expenditure]), allexcept(activity_expenditure_apportioned,activities[iati-identifier]))* ((activity_expenditure_apportioned[country_percentage] / 100) * activity_expenditure_apportioned[sector_percentage] / 100)And if anyone is interested, here's how I've got it connected in the data model (but yet to do any rigourous testing, so grain of salt, etc)