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 tryi...
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)