Modelling XML IATI data in PowerBI - multiple categories, apportioned expenditure

[Anonymous]

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]

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)

[amitchandak · Accepted solution]

@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