Create a Calculated Column from Different Tables

[philpringuer]

Hi Team - apologies in advance; new to PBI...

I'm working with, say, a table containing thousands of burns.
Each burn has a PLANNED AREA, and a DISTRICT that it falls within.

I'm able to summarise, by DISTRICT (row headings), the total PLANNED AREA - lets call this calculated matrix Table A.  all good.

I have another table - Table B - containing, per DISTRICT, the total area we WANT to achieve - lets call that TARGET AREA.

While there can be many burns per District in Table A; there's only one aspirational TARGET AREA, per District, in Table B.

Presumably, I need to create a relationship between the 2 tables, based on DISTRICT.

My question: How do I add the TARGET AREA (static) values (from Table B) per DISTRICT, to the matrix (calculated) summary in Table A?

Ultimately, trying to arrive at:
DISTRICT A is has a total (sum) PLANNED AREA of (say) 10,000 hectares.
DISTRICT A has a static TARGET AREA of (say) 20,000 ha.
Thus, DISTRICT A has done planning for 10,000 / 20,000 = 50% planned.

DISTRICTS ultimately belong to REGIONS via a heirarchy.

But that may not be relevant to the immediate challenge....

Any / all help would be 

Calculated PLANNED AREA totals - would like to add in the aspirational, static TARGET HECTARES

Above shows DISTRICTS, rolling up to REGIONS, with 2nd column successfully calculating how much we've PLANNED to burn - but need to display, alongside that, how much we ASPIRE to burn.  Ignore COUNTS, but it's the same problem / challenge - planned vs. targets.

[TeigeGao · Accepted solution]

Hi philpringuer,

According to your description, my understanding is that you want to create a new table which contains the summarize data and target data of each DISTRICT.

In this scenario, we can create two calculated column in Table B like below:

TOTAL PLANNED AREA = CALCULATE(SUM(TableA[PLANNED AREA]),FILTER(ALL(TableA),TableA[DISTRICT]= TableB[DISTRICT]))

Done Planning = DIVIDE(TableB[TOTAL PLANNED AREA],TableB[TARGET AREA])

The result will like below:

For the problem how to display the hierarchy (REGIONS -> DISTRICTS), we can drag the DISTRICTS to REGIONS to create a hierarchy. Then drag the hierarchy and TOTAL PLANNED AREA and other columns to Matrix. After that click the below button 2.

The result will like below:

Best Regards,

Teige

[philpringuer]

Hi Teige - thank-you v.much for your response - I was away from work Friday, and am trying out your answer now ... and will confirm once I've followed your suggestions.

Again, thanks for taking the time to help me.

phil

[philpringuer]

... and, it's a WINNER!  

Thanks so much Teige - wherever you are, I owe you a coffee / beer - if you're visiting Melbourne, you can collect.  Now off to understand  it.

phil