Help with progress against targets roll-up

[yes_thisischris]

Hi all!

I'm a new power BI user helping an NGO transition away from Excel for their daily reports (terrible, gigabyte monstrosities).

I've been working on a problem for the last few days and I'm stumped. The organization's HQ sets yearly targets for helping clients in different regions, like so:

Each region then has its own large dataset where it keeps track of incremental progress towards these targets, like so:

I've appended these regional lists together in the query editor and have created a relationship using the Region column between this table and the targets table. So far so good.

What has me stumped is that the program manager wants a progress against targets table that looks like this:

I can't figure out how to do it!

I thought I had solved it by merging the regional data into the target table and then aggregating it--with the intention of using an un-pivot operation to then reorganize the resulting table into | target name | target | progress |--but the files are so big that I had to cancel the operation after 6 hours (this seems to be a known issue with aggregation..). Any ideas would be a huge help!

[v-yuta-msft]

Hi yes_thisischris,

To enhance the performance of table operation in power query, I would recommend you to use R script. Please refer to: https://docs.microsoft.com/en-us/power-bi/desktop-r-scripts.

Regards,

Jimmy Tao